Retrieving Data Efficiently with Eager LoadingProblemYou'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. SolutionUsing 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> DiscussionThe 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. Figure 3-2. The results of the find method and eager loading, displayedSee Also
|