Best way to execute multiple table join?

Say i have multiple tables (models) and I need to select an item in
one table, and all of the data in the other models to get a compete row.

What’s the best way to join them so that I get the data I need in one
query?

I know I can say:

p = Person.find_by_id(100, :include => [ :books]);

If Person and Book have a “has_many” relationship, But what if Person
has Books and Books has a has_many relationship to Categories and I
want both models to be loaded at the same time?

This is easy in other languages, but what’s the proper “Rails way” of
doing this?

-john

p = Person.find_by_id(100, :include => [ {:books => :categories} ]);

Can this be repeated indefinitely ?

as in…

p = Person.find_by_id(100, :include => [ {:books => { :categories
=> :subcategories }} ]);

Thanks,
-john

You bet :slight_smile:

Within reason - the database query gets a little more gnarly each
time. You might also eventually run into limits on the number of
joins allowed in a single query
You also want to be careful when loading multiple sibling has_many, eg

Person.find_by_id(123, :include => [:books, :magazines]).

If a person had 100 books and 100 magazines then this would end up
with rails handling 10000 rows of results (100x100), which it
currently does so in a not very speedy way.

Fred

I guess I should have added a qualifier, as in “within reason” to my
query :wink:

It’s unfortunate that there’s no way to specify limit clauses to the
subquery. (or is there? I know that :limit => 5 works on most finder
methods.)

-john

True, nothing is free, but within reason, this works very well. I do
wish there was a way to limit the columns returned through select, and
from what I have read, that is a future planned feature, but
unfortunately, currently, you get every column from all tables. When he
said indefinitely, I assumed he realized that building 10,000 objects
would take a bit of time, but I guess I assume a little to much
sometimes. :slight_smile:

William P. wrote:

I do
wish there was a way to limit the columns returned through select, and
from what I have read, that is a future planned feature, but
unfortunately, currently, you get every column from all tables.

Won’t the :select option give you this?

Eric

On 22 Oct 2007, at 09:47, Eric A. wrote:

William P. wrote:

I do
wish there was a way to limit the columns returned through select,
and
from what I have read, that is a future planned feature, but
unfortunately, currently, you get every column from all tables.

Won’t the :select option give you this?

Currently, :include overwrites :select

Fred

On Oct 22, 1:59 am, Frederick C. [email protected]
wrote:

Won’t the :select option give you this?

Currently, :include overwrites :select

Fred

Eric

you could try this (I haven’t myself

http://assertbuggy.blogspot.com/2007/05/activerecord-select-with-include.html
http://code.google.com/p/ar-select-with-include/

On Oct 22, 4:51 am, gene tani [email protected] wrote:

and

you could try this (I haven’t myself

http://assertbuggy.blogspot.com/2007/05/activerecord-select-with-incl...http://code.google.com/p/ar-select-with-include/

BTW i htink the correct patch referred to on the Google code page

http://dev.rubyonrails.org/ticket/7147