Executing Custom Queries with find_by_sql

Problem

You've used Active Record's find method as well as the dynamic attribute-based finders for simple queries. As useful as these methods are, there is sometimes no better tool than SQL for complex database queries. You want to use SQL to create a report from your database and store the results of the query in an array of Active Record objects.

Solution

You have a database with movies and genres tables. The movies table contains sales data for each movie. The following migration sets up these tables and populates them with some data:

db/migrate/001_build_db.rb:

class BuildDb < ActiveRecord::Migration
 def self.up
 create_table :genres do |t|
 t.column :name, :string
 end
 create_table :movies do |t|
 t.column :genre_id, :integer
 t.column :name, :string
 t.column :sales, :float
 t.column :released_on, :date
 end
 genre1 = Genre.create :name => 'Action'
 genre2 = Genre.create :name => 'Biography'
 genre3 = Genre.create :name => 'Comedy'
 genre4 = Genre.create :name => 'Documentary'
 genre5 = Genre.create :name => 'Family'
 Movie.create :genre_id => genre1,
 :name => 'Mishi Kobe Niku',
 :sales => 234303.32,
 :released_on => '2006-11-01'
 Movie.create :genre_id => genre3,
 :name => 'Ikura',
 :sales => 8161239.20,
 :released_on => '2006-10-07'
 Movie.create :genre_id => genre2,
 :name => 'Queso Cabrales',
 :sales => 3830043.32,
 :released_on => '2006-08-03'
 Movie.create :genre_id => genre4,
 :name => 'Konbu',
 :sales => 4892813.28,
 :released_on => '2006-08-08'
 Movie.create :genre_id => genre1,
 :name => 'Tofu',
 :sales => 13298124.13,
 :released_on => '2006-06-15'
 Movie.create :genre_id => genre2,
 :name => 'Genen Shouyu',
 :sales => 2398229.12,
 :released_on => '2006-06-20'
 Movie.create :genre_id => genre3,
 :name => 'Pavlova',
 :sales => 4539410.59,
 :released_on => '2006-06-12'
 Movie.create :genre_id => genre1,
 :name => 'Alice Mutton',
 :sales => 2038919.83,
 :released_on => '2006-02-21'
 end
 def self.down
 drop_table :movies
 drop_table :genres
 end end

Set up the one-to-many relationship between genres and movies with the following model definitions:

app/models/movie.rb:

class Movie < ActiveRecord::Base
 belongs_to :genre end

app/models/genre.rb:

class Genre < ActiveRecord::Base
 has_many :movies end

In the Movies Controller, call the find_by_sql method of the Movie class. You can store the results in the @report array.

app/controllers/movies_controller.rb:

class MoviesController < ApplicationController
 def report
 @report = Movie.find_by_sql(" 
 select 
 g.name as genre_name, 
 format(sum(m.sales),2) as total_sales
 from movies m
 join genres g
 on m.genre_id = g.id
 where m.released_on > '2006-08-01'
 group by g.name 
 having sum(m.sales) > 3000000 
 ")
 end end 

The view then inserts the report into HTML:

app/views/movies/report.rhtml:

<h1>Report</h1>
<table >
 <tr>
 <th>Genre</th> 
 <th>Total Sales</th> 
 </tr>
 <% for item in @report %>
 <tr> 
 <td><%= item.genre_name %></td> 
 <td>$<%= item.total_sales %></td> 
 </tr> 
 <% end %>
</table>

Discussion

The report method in the Movies Controller calls the find_by_sql method, which executes any valid SQL statement. The find_by_sql method returns the attributes that are in the select clause of the SQL query. In this case, they are stored in an instance array, and become available to the report view for display.

Note that the model class definitions are not necessary for find_by_sql to work. find_by_sql is just running an SQL query against your database; the query doesn't know or care about your Active Record model classes.

is the output of the report on movie sales by genre.

Figure 3-5. The results of a simple report using find_by_sql

It's important to keep in mind that Active Record is not intended to replace SQL but rather to provide a more convenient syntax for simple attribute or association lookups. SQL is an excellent tool for querying a relational database. If you find yourself getting into complex joins of a half dozen or more tables, or you just feel more comfortable solving a problem with pure SQL, it's perfectly acceptable to do so.

If you use complex queries, it would be nice to not have to repeat them throughout your application. A good practice is to add custom accessor methods to your models; these methods make queries that you use more than once. Here's a method that we've added to the Movie class called find_comedies:

class Movie < ActiveRecord::Base
 belongs_to :genre
 def self.find_comedies()
 find_by_sql("select * from movies where genre_id = 2")
 end end

You can test this method from the Rails console:

>> Movie.find_comedies
=> [#<Movie:0x40927b20 @attributes={"name"=>"Queso Cabrales",
"genre_id"=>"2", "sales"=>"3.83004e+06", "released_on"=>"2006-08-03",
"id"=>"3"}>, #<Movie:0x40927ae4 @attributes={"name"=>"Genen Shouyu",
"genre_id"=>"2", "sales"=>"2.39823e+06", "released_on"=>"2006-06-20",
"id"=>"6"}>]

Notice that find_by_sql returns an array of IDs. This array is passed to the find method, which returns an array of Movie objects.

See Also