deeply nested joins in activerecord

Question

I'm trying to optimize my queries throughout my site.

I have 3 models, Photo, Person, Appearances

Photo has_many appearances, and many people through appearances Appearances table just has a person_id, photo_id

Now, if I were to do a search on a 'Person' and I wanted to eager load their appearances and photos, I would do something like this:

 Person.joins(:appearances => :photo).limit(5)

Now, I'm not sure if this is ideal, but hypothetically my person has appearances which belongs to a photo, which in turn has appearances and other people. I don't even know if or how you'd do this in vanilla SQL, but I am just curious if this is possible.

  Person.joins(:appearances => :photo => :appearaces => :person).limit(5)

This syntax results in errors, again, I'm just curious, I'm handling getting the appearances and people of a photo inside my views, I just wanted to experiment with load times and see if this was even possible.

Answer 1

For 'eager loading' you use .include; .join is for performing INNER JOIN functionality. It seems likely that for your case you will be using both (the join to get the photo, and the includes to get the additional people). I make this point because .join alone will not perform eager loading (and so it will make the queries afterward when the associated entities are accessed).

If you wish to eager-load nested associations, you can use the syntax outlined in the Ruby on Rails guide at http://guides.rubyonrails.org/active_record_querying.html#eager-loading-multiple-associations

Would something along these lines work?

Photo.joins(:appearances => :person).includes(:appearances => :person).limit(5)

Or do you need to start with the person?

Person.joins(:appearances => :photo).includes(:appearances => :photo => :appearaces => :person).limit(5)

Also, as a final (small) note: you have ":appearances" spelled incorrectly in your last line of code (it states ":appearaces"), which could cause issues.

EDIT: After some minor testing, it appears that nesting the include associations in the

a => b => c => b => a

form doesn't work. However, the following form does:

a => [b => [c => [b => a]]]

Though, of course, it isn't quite as pretty.

Meaning, using the following code:

Person.includes(:appearances => [:photo => [:appearances => :person]]).find(38)       

The following SQL was generated:

Person Load (0.3ms)  SELECT `persons`.* FROM `persons` WHERE `persons`.`id` = 38 LIMIT 1
Appearance Load (0.3ms)  SELECT `appearances`.* FROM `appearances` WHERE (`appearances`.person_id = 38)
Photo Load (0.3ms)  SELECT `photos`.* FROM `photos` WHERE (`photos`.`id` = 1904)
Appearance Load (0.3ms)  SELECT `appearances`.* FROM `appearances` WHERE (`appearances`.photo_id = 1904)
Person Load (0.3ms)  SELECT `persons`.* FROM `persons` WHERE (`persons`.`id` IN (38,346))

Eager loading the additional Persons from all Photos that include Person with id #38