Chaining :include’s in Rails to reduce the number of SQL queries

Say you have the following data model

and you want to execute a single query that returns all the data at once within the ActiveRecord tables, with the proper rails associations between them. Wouldn’t it be nice if you could do something like

A.find(:all, :include => :b => :c => :d => :e) #v1

? Though this is not even valid ruby code, it actually comes very close to what you can do in Ruby on Rails. To get this right, let’s take a closer look at the rails associations within the class definitions:

class A < ActiveRecord::Base
  belongs_to :b
end
class B < ActiveRecord::Base
  has_many :as
  has_many :cs
end
class C < ActiveRecord::Base
  belongs_to :b
  has_one :d
end
class D < ActiveRecord::Base
  belongs_to :c
  has_and_belongs_to_many :es
end
class E < ActiveRecord::Base
  has_and_belongs_to_many :ds
end

Let’s try the rails code again, putting an ’s’ after the :c and :e as required by rails in order to denote they are “many”-type associations:

A.find(:all, :include => :b => :cs => :d => :es) #v2

That’s closer, but still not valid ruby code. To fix that, think of the => operator as being right-associative, and instead of putting in parentheses (), put in curly braces {} in order to create nested hashes:

A.find(:all, :include => {:b => {:cs => {:d => :es}}}) #v3

That’s it! Looking in the logs, we see that this only produced a single query, with all the desired SQL joins:

A Load Including Associations (0.001088) SELECT `as`.`id` AS t0_r0, `as`.`b_id` AS t0_r1, `bs`.`id` AS t1_r0, `cs`.`id` AS t2_r0, `cs`.`b_id` AS t2_r1, `cs`.`d_id` AS t2_r2, `ds`.`id` AS t3_r0, `ds`.`c_id` AS t3_r1, `es`.`id` AS t4_r0 FROM `as` LEFT OUTER JOIN `bs` ON `bs`.id = `as`.b_id LEFT OUTER JOIN `cs` ON cs.b_id = bs.id LEFT OUTER JOIN `ds` ON ds.c_id = cs.id LEFT OUTER JOIN `ds_es` ON `ds_es`.d_id = `ds`.id LEFT OUTER JOIN `es` ON `es`.id = `ds_es`.e_id

With this tool in mind, you can use this in any ActiveRecord function that accepts the :include option to reduce the number of times the rails app hits the database, and ultimately speed up your rails application.