Rails - Jan 2007 - loading ActiveRecords via a complex query

Ok, I have a many_to_many relationship between two tables (A, B)
implemented by a join table (J).  J has two columns:  a_id and b_id.

Currently, I use a query like this:
<code>
recs =  A.find :all, :select => ''A.*'',
                           :joins => '', B, J'',
                           :conditions => ''A.id = J.a_id AND J.b_id
B.id AND B.somefield = somevalue ''
</code>

That works well, but is there a more Rails like way to do it?

Also, is there a more efficient way to do it?  Notice in my RoR code,
I''m joining 3 tables together in a single SQL statement.  Would it be
faster to select ids in an SQL call joining only 2 tables, then calling
A.find :all, ids?

In code:
<code>
ids_array = "SELECT J.a_id FROM J, B WHERE J.b_id = B.id AND
B.somefield = somevalue"
recs = A.find :all, ids_array
</code>

How do I efficiently execute that query and get an array of ids in
return?

Thanks for the help.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

You have two options.

has_and_belongs_to_many :foo

Each of the two models that''s gonna be joined has a
has_and_belongs_to_many :other_model in it. Say your models are called
Book and Author - the table would then be named authors_books (plural
and in alphabetic order). The authors_books table would have two
columns: book_id and author_id. No primary key.

has_many :foo, :through => :bar

A bit to intricate to explain. Look at this pastie:
http://pastie.caboo.se/34094


On Jan 18, 9:15 pm, "Christopher J. Bottaro"
<cjbott...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Ok, I have a many_to_many relationship between two tables (A, B)
> implemented by a join table (J).  J has two columns:  a_id and b_id.
>
> Currently, I use a query like this:
> <code>
> recs =  A.find :all, :select => ''A.*'',
>                            :joins => '', B, J'',
>                            :conditions => ''A.id = J.a_id AND
J.b_id > B.id AND B.somefield = somevalue ''
> </code>
>
> That works well, but is there a more Rails like way to do it?
>
> Also, is there a more efficient way to do it?  Notice in my RoR code,
> I''m joining 3 tables together in a single SQL statement.  Would it
be
> faster to select ids in an SQL call joining only 2 tables, then calling
> A.find :all, ids?
>
> In code:
> <code>
> ids_array = "SELECT J.a_id FROM J, B WHERE J.b_id = B.id AND
> B.somefield = somevalue"
> recs = A.find :all, ids_array
> </code>
>
> How do I efficiently execute that query and get an array of ids in
> return?
> 
> Thanks for the help.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

I know how join tables work pertaining to Rails models.  I''m asking
about how to load records using complex queries that involve join
tables.

See my pastie:
http://pastie.caboo.se/34129

Thanks.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---