Snippets
DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

The Beauty Of A Nested ':include' Option

05.23.2006
| 121744 views |
  • submit to reddit 
        So you've got a lot of stuff, spread over a lot of tables, and now you want to scoop it all up and display it all in the same view - oh, and you don't want ActiveRecord to perform a bazillion subsidiary SQL lookups along the way. The answer - a nested :include clause.

The example:

A model...


class Organizer < ActiveRecord::Base
	has_many :events
end

class Event < ActiveRecord::Base
	belongs_to :organizer
	has_many :bookings
end

class Booking < ActiveRecord::Base
	has_many :sessions
	belongs_to :event
end

class Session < ActiveRecord::Base
	belongs_to :booking
end


... and in my controller i want to...


Booking.find_all_by_date(Date.today)


Now this all works fine... up until a certain line in my '_booking' view partial:


<%= h(booking.event.organizer.name) %>


...and a little further along, when i do a:


<%= render :partial => 'session', :collection => booking.sessions %>


Both of which work fine too. Unfortunately, my view has to execute the 'booking' partial a total of three times, and each time it does so, ActiveRecord performs three additional SQL queries - one for the events table lookup, one for the organizers table lookup and one for the sessions table lookup. Along with the original 'bookings' query, that comes to a total of 10 SQL queries to display the details of three bookings. Hardly very efficient.

:include to the rescue (maybe):


Booking.find_all_by_date(Date.today, :include => [:event, :sessions])


Sure enough, this pre-loads (or eager loads) the events and sessions table info into the model, thus allowing me to use a line like 'booking.event.title', or 'booking.sessions[0].start_time' without firing off another SQL query. So now my query count drops to just four - one to fetch the combined booking/event/sessions data and the other three to fetch each organizer. All good.

So, then I tried this:


Booking.find_all_by_date(Date.today, :include => [:event, :organizer, :sessions])


Wrong!!

I got an error because ':organizer' is not a valid association of the Booking model (ie, there's no 'has_one', 'has_many' or 'belongs_to' referring to the Organizer model from the Booking model)

I flailed around on the internet for a few hours searching for (and trying out) a number of solutions - mostly revolving around 'find_by_sql'. Though a couple of these solutions successfully fetched the data, they didn't then populate the model objects correctly. Instead, 'find_by_sql' seems to add new accessors to my Booking class - this would allow me to use a line like 'booking.organizer.name', but a call to the real object (booking.event.organizer.name) would still generate a SQL query. Not acceptable.

The most interesting possibility I found was using a :through clause. This allows you to refer to one association 'through' another like this:


class Booking < ActiveRecord::Base
	belongs_to :event
	belongs_to :organizer, :through => :event
end


Unfortunately, the :through clause only works when you're passing :through a 'has_many' association. It doesn't work for 'has_one' or 'belongs_to' (didn't try 'has_and_belongs_to').

So, finally, in a fit of mingled frustration/inspiration (and knowing the nested nature of some of the rails code) i tried this:


Booking.find_all_by_date(Date.today, :include => [{:event, :organizer}, :sessions ])


My reasoning: well, since :organizer is a valid association of the Event model, I thought that if I packaged those two together in a kind of sub-include, it might actually do a nested call to whichever magic rails method constructs the SQL queries.

I've since found out that the correct syntax for this clause should be  ':include => [{:event => :organizer}, :sessions ]'. Apparantly when you throw an even number of comma-separated items into a hash literal, ruby groups them into :key => :value pairs for you - what an amazing language it is! :-) So the corrected code snipped should be:


Booking.find_all_by_date(Date.today, :include => [{:event => :organizer}, :sessions ])


And Eureka!!

A single (hefty) SQL query fetched all relevant data, whereupon ActiveRecord dutifully (and apparently recursively) populated all the model objects that my view template needed.

I'm happy.

For reference: I'm using Ruby 1.8.4, Rails 1.1 and the SQLite3 database.

Comments

Aashish Kiran replied on Fri, 2012/11/23 - 5:59am

 Hi,

Thanks for spending time in writting this post.  It is a good  post.  It added a joy in finding out hidden beauty of rails.

Thanks for all the help through this post.

Keep  posting  !!

Snippets Manager replied on Tue, 2012/01/03 - 6:49pm

Nevermind, figured it out. If you want to order the specific eager-loaded members, you just need to specify the table name in your :order clause. Example: Booking.find_all_by_date(Date.today, :order => 'events.created_at DESC', :include => [{:event => :organizer}, :sessions ])

Snippets Manager replied on Tue, 2012/01/03 - 6:49pm

Anyone know if you can include an :order option to order the eager-loaded queries?

Snippets Manager replied on Tue, 2009/02/24 - 9:50am

bestest snippet.

Snippets Manager replied on Fri, 2008/04/04 - 4:04pm

Very useful. Thanks much.

Snippets Manager replied on Sat, 2007/06/30 - 10:02pm

Thanks a bunch! I spent the whole afternoon looking for the elegant solution, and this was it.

Snippets Manager replied on Wed, 2007/06/20 - 1:11pm

You might want to consider using :include option in the find to select only the columns of interest from the :include table set. e.g., WorkUnit.find(:all, :select => "work_units.id, parts.job_id, operations.name" :order => 'parts.title', :include => [{:part => :job}, :operation]) Note: this does require you to install the select_with_include gem

Snippets Manager replied on Tue, 2007/06/05 - 12:38pm

Note that the nesting can go several levels deep. This worked for me: :include => [:sometable, {:torrent_entry => {:entry => [:category, :tags, {:attrib_values => :attrib_key}]}}])

Hassal Fathi replied on Fri, 2007/01/19 - 1:03am

Thanks a lot for this tip, thairond! I divided by 1000 the number of my SQL requests :) @willcodeforfoo, I just discovered that you can type this : @organizer=Organizer.find(:first, :include => [{:events => {:bookings => :sessions}}]) So you can indeed eagerly load through more than one level. You won't be able to access @organizer.bookings directly though, you'll have to go through : @organizer.events.first.bookings Bye, Eric

Snippets Manager replied on Mon, 2012/05/07 - 2:24pm

Oh, and it doesn't seem to be working for associations deeper than one level, does it?

Snippets Manager replied on Mon, 2012/05/07 - 2:24pm

That's pretty slick, nice find! Thank goodness for Rails so instead of writing: SELECT work_units.`id` AS t0_r0, work_units.`part_id` AS t0_r1, work_units.`operation_id` AS t0_r2, work_units.`estimated_hours` AS t0_r3, work_units.`scheduled_hours` AS t0_r4, work_units.`status` AS t0_r5, work_units.`quantity_completed` AS t0_r6, parts.`id` AS t1_r0, parts.`job_id` AS t1_r1, parts.`title` AS t1_r2, parts.`width` AS t1_r3, parts.`height` AS t1_r4, parts.`quantity` AS t1_r5, parts.`filename` AS t1_r6, jobs.`id` AS t2_r0, jobs.`job_code` AS t2_r1, jobs.`due_by` AS t2_r2, jobs.`production_objective` AS t2_r3, jobs.`title` AS t2_r4, jobs.`customer_id` AS t2_r5, jobs.`created_on` AS t2_r6, jobs.`customer_po` AS t2_r7, jobs.`closed_at` AS t2_r8, jobs.`invoice_number` AS t2_r9, jobs.`invoice_amount` AS t2_r10, jobs.`hours_cache` AS t2_r11, jobs.`csr_id` AS t2_r12, jobs.`sales_id` AS t2_r13, jobs.`carrier_id` AS t2_r14, jobs.`delivery_instructions` AS t2_r15, jobs.`installation` AS t2_r16, jobs.`installation_instructions` AS t2_r17, operations.`id` AS t3_r0, operations.`schedulable` AS t3_r1, operations.`code` AS t3_r2, operations.`name` AS t3_r3, operations.`department_id` AS t3_r4, operations.`hourly_rate` AS t3_r5, operations.`markup` AS t3_r6, operations.`billable` AS t3_r7 FROM work_units LEFT OUTER JOIN parts ON parts.id = work_units.part_id LEFT OUTER JOIN jobs ON jobs.id = parts.job_id LEFT OUTER JOIN operations ON operations.id = work_units.operation_id ORDER BY parts.title we can write @work_units = WorkUnit.find(:all, :order => 'parts.title', :include => [{:part => :job}, :operation]) According to the rdocs, belongs_to and has_many accept an :include option. I'm wondering if this will do the eager loading on every query rather than having to explicitly state it in the find()?

Snippets Manager replied on Sun, 2006/05/28 - 1:58am

I would like to thank you too for this, with your post I might manage to break the habit of littering my models with find_by_sql :)

Joe Martinez replied on Tue, 2006/05/23 - 10:18pm

This is a great post!

Snippets Manager replied on Mon, 2012/05/07 - 2:23pm

Thank you for posting this. This is a handy snippet I will file away in my brain for later use...
"Starting from scratch" is seductive but disease ridden
-Pithy Advice for Programmers