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.

shows all of the gallery information gathered by the SQL query that generated find.

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

See Also