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} ]);

John Adams wrote:

Can this be repeated indefinitely ?

as in...

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

Thanks,
-john

You bet :slight_smile:

John Adams wrote:

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

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:

Frederick Cheung wrote:

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

William Pratt 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

William Pratt 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

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/

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

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