# Loading ActiveRecords via a complex query

**URL:** <https://www.ruby-forum.com/t/loading-activerecords-via-a-complex-query/82509>\
**Category:** Rails\
**Created:** [January 19, 2007, 4:30pm UTC](https://www.ruby-forum.com/t/loading-activerecords-via-a-complex-query/82509 "2007-01-19T16:30:58Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Christopher\_JSBott](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ee7513/32.png) [@Christopher\_JSBott](https://www.ruby-forum.com/u/Christopher_JSBott)\
**Post date:** [January 19, 2007, 4:30pm UTC](https://www.ruby-forum.com/t/loading-activerecords-via-a-complex-query/82509/1 "2007-01-19T16:30:58Z")

</div>

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 => 'A.id = J.a_id AND J.b_id =
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 = 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  
return?

Thanks for the help.

---

<div class="post-metadata">

**Author:** ![Christopher\_JSBott](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ee7513/32.png) [@Christopher\_JSBott](https://www.ruby-forum.com/u/Christopher_JSBott)\
**Post date:** [September 25, 2007, 10:30pm UTC](https://www.ruby-forum.com/t/loading-activerecords-via-a-complex-query/82509/2 "2007-09-25T22:30:57Z")

</div>

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](http://pastie.caboo.se/34094)

On Jan 18, 9:15 pm, “Christopher J. Bottaro” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![Christopher\_JSBott](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ee7513/32.png) [@Christopher\_JSBott](https://www.ruby-forum.com/u/Christopher_JSBott)\
**Post date:** [September 25, 2007, 10:43pm UTC](https://www.ruby-forum.com/t/loading-activerecords-via-a-complex-query/82509/3 "2007-09-25T22:43:34Z")

</div>

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](http://pastie.caboo.se/34129)

Thanks.