Recipe 3.9. Retrieving Data Efficiently with Eager Loading

Problem

You've got data in a table containing records that reference a parent record from a second table, as well as child records in a third table. You want to retrieve all the objects of a certain type, including each object's associated parent and children. You could gather this information by looping over each object and performing additional queries within the loop, but that's a lot of separate hits to the database. You want a way to gather all of this information using as few queries as possible.

Solution

Using Active Record's eager loading, you can fetch objects from a model and include associated objects, all with a single database query.

Assume you have a photography web site that displays galleries containing photos by different photographers. This database is defined by the following migration:

db/migrate/001_build_db.rb:

class BuildDb < ActiveRecord::Migration   def self.up     create_table :photographers do |t|       t.column :name,            :string     end     create_table :galleries do |t|       t.column :photographer_id, :integer       t.column :name,            :string     end     create_table :photos do |t|       t.column :gallery_id,      :integer       t.column :name,            :string       t.column :file_path,       :string     end   end   def self.down     drop_table :photos     drop_table :galleries     drop_table :photographers   end end

The relationships between photographers, galleries, and photos are set up in each model's class definition.

models/photographer.rb:

class Photographer < ActiveRecord::Base   has_many :galleries end

app/models/gallery.rb:

class Gallery < ActiveRecord::Base   has_many :photos   belongs_to :photographer end

app/models/photo.rb:

class Photo < ActiveRecord::Base   belongs_to :gallery end

Finally, populate your database with the following data set:

insert into photographers values (1,'Philip Greenspun'); insert into photographers values (2,'Mark Miller'); insert into galleries values (1,1,'Still Life'); insert into galleries values (2,1,'New York'); insert into galleries values (3,2,'Nature'); insert into photos values (1,1,'Shadows','photos/img_5411.jpg'); insert into photos values (2,1,'Ice Formations','photos/img_6386.jpg'); insert into photos values (3,2,'42nd Street','photos/img_8419.jpg'); insert into photos values (4,2,'The A Train','photos/img_3421.jpg'); insert into photos values (5,2,'Village','photos/img_2431.jpg'); insert into photos values (6,2,'Uptown','photos/img_9432.jpg'); insert into photos values (7,3,'Two Trees','photos/img_1440.jpg'); insert into photos values (8,3,'Utah Sunset','photos/img_3477.jpg');

To use eager loading, add the :include option to Active Record's find method, as in the following Galleries Controller. The data structure returned is stored in the @galleries instance variable.

app/controllers/galleries_controller.rb:

class GalleriesController < ApplicationController   def index     @galleries = Gallery.find(:all, :include => [:photos, :photographer])   end end

In your view, you can loop over the @galleries array and access information about each gallery, its photographer, and the photos it contains:

app/views/galleries/index.rhtml:

<h1>Gallery Results</h1> <ul>   <% for gallery in @galleries %>     <li><b><%= gallery.name %> (<i><%= gallery.photographer.name %></i>)</b>       <ul>         <% for photo in gallery.photos %>           <li><%= photo.name %> (<%= photo.file_path %>)</li>         <% end %>       </ul>     </li>    <% end %> </ul>

Discussion

The solution uses the :include option of the find method to perform eager loading. Since we called the find method of the Gallery class, we can specify the kinds of objects to be retrieved by listing their names as they appear in the Gallery class definition.

So, since a gallery has_many :photos and belongs_to a :photographer, we can pass :photos and :photographer to :include. Each association listed in the :include option adds a left join to the query created behind the scenes. In the solution, the single query created by the find method includes two left joins in the SQL it generates. In fact, that SQL looks like this:

SELECT      photographers.`name' AS t2_r1,      photos.`id' AS t1_r0,      photos.`gallery_id' AS t1_r1,      galleries.`id' AS t0_r0,      photos.`name' AS t1_r2,     galleries.`photographer_id' AS t0_r1,      photos.`file_path' AS t1_r3,     galleries.`name' AS t0_r2,      photographers.`id' AS t2_r0  FROM galleries  LEFT OUTER JOIN photos       ON photos.gallery_id = galleries.id  LEFT OUTER JOIN photographers      ON photographers.id = galleries.photographer_id

There is a lot of aliasing going on here that's used by Active Record to convert the results into a data structure, but you can see the inclusion of the photos and photographers tables at work.

Active Record's eager loading is convenient, but there are some limitations to be aware of. For example, you can't specify :conditions that apply to the models listed in the :include option.

Figure 3-2 shows all of the gallery information gathered by the SQL query that generated find.

Problem

Solution

Discussion

Figure 3-2. The results of the find method and eager loading, displayed

See Also