The beauty of a nested ':include' option

thalrond (Philip Martin) — Tue, 23 May 2006 19:20:36 GMT

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.

Doing a has_and_belongs_to_many against a single table

peter (Peter Cooperx) — Sat, 14 May 2005 09:44:21 GMT

Found via the RoR wiki. Solution courtesy of Jakob S.

Assuming a schema like such:

create table node (

  id integer primary key,

  name varchar(32)

);



create table nodelink (

  source_id integer,

  destination_id integer

);

A Rails association like this would supposedly do the job:

has_and_belongs_to_many :nodes, :join_table => 'nodelink', :association_foreign_key => 'destination_id', :foreign_key => 'source_id'

DZone Snippets: relationships code

The beauty of a nested ':include' option

Doing a has_and_belongs_to_many against a single table