Forum: Ruby on Rails loading ActiveRecords via a complex query

Announcement (2017-05-07): is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see and for other Rails- und Ruby-related community platforms.
7ac9652c07e2ce83b8288663460f5fa8?d=identicon&s=25 Christopher J. Bottaro (Guest)
on 2007-01-19 16:30
(Received via mailing list)
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:
recs =  A.find :all, :select => 'A.*',
                           :joins => ', B, J',
                           :conditions => ' = J.a_id AND J.b_id = AND B.somefield = somevalue '

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:
ids_array = "SELECT J.a_id FROM J, B WHERE J.b_id = AND
B.somefield = somevalue"
recs = A.find :all, ids_array

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

Thanks for the help.
70ca58d0e0e0eabbdb74d177417d09d7?d=identicon&s=25 August Lilleaas (leethal)
on 2007-09-25 22:30
(Received via mailing list)
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:

On Jan 18, 9:15 pm, "Christopher J. Bottaro" <>
7ac9652c07e2ce83b8288663460f5fa8?d=identicon&s=25 Christopher J. Bottaro (Guest)
on 2007-09-25 22:43
(Received via mailing list)
I know how join tables work pertaining to Rails models.  I'm asking
about how to load records using complex queries that involve join

See my pastie:

This topic is locked and can not be replied to.