Recipe 3.7. Retrieving Records with find

Problem

You want to retrieve an Active Record object that represents a specific record in your database or a set of Active Record objects that each correspond to items in the database, based on specific conditions being met.

Solution

First, you need some data to work with. Set up an employees table in your database, and populate it with a set of employees with their names and hire dates. The following migration does this:

db/migrate/001_create_employees.rb:

class CreateEmployees < ActiveRecord::Migration   def self.up     create_table :employees do |t|       t.column :last_name,  :string       t.column :first_name, :string       t.column :hire_date,  :date     end     Employee.create :last_name => "Davolio",                     :first_name => "Nancy",                     :hire_date => "1992-05-01"     Employee.create :last_name => "Fuller",                     :first_name => "Andrew",                     :hire_date => "1992-08-14"     Employee.create :last_name => "Leverling",                     :first_name => "Janet",                     :hire_date => "1992-04-01"     Employee.create :last_name => "Peacock",                     :first_name => "Margaret",                     :hire_date => "1993-05-03"     Employee.create :last_name => "Buchanan",                     :first_name => "Steven",                     :hire_date => "1993-10-17"     Employee.create :last_name => "Suyama",                     :first_name => "Michael",                     :hire_date => "1993-10-17"     Employee.create :last_name => "King",                     :first_name => "Robert",                     :hire_date => "1994-01-02"     Employee.create :last_name => "Callahan",                     :first_name => "Laura",                     :hire_date => "1994-03-05"     Employee.create :last_name => "Dodsworth",                     :first_name => "Anne",                     :hire_date => "1994-11-15"   end   def self.down     drop_table :employees   end end

To find the record with an ID of 5, for example, pass 5 to find:

>> Employee.find(5) => #__"1993-10-17", "id"=>"5", "first_name"=>"Steven", "last_name"=>"Buchanan"}>

In your controller, you assign the results to a variable. In practice, the ID would usually be a variable as well.

employee_of_the_month = Employee.find(5)

If you pass an array of existing IDs to find, you get back an array of Employee objects:

>> team = Employee.find([4,6,7,8]) => [#__"1993-05-03", "id"=>"4", "first_name"=>"Margaret", "last_name"=>"Peacock"}>, #<Employee:0x40b1ffe0 @attributes={"hire_date"=>"1993-10-17", "id"=>"6", "first_name"=>"Michael", "last_name"=>"Suyama"}>, #<Employee:0x40b1ffa4 @attributes={"hire_date"=>"1994-01-02", "id"=>"7", "first_name"=>"Robert", "last_name"=>"King"}>, #<Employee:0x40b1ff68 @attributes={"hire_date"=>"1994-03-05", "id"=>"8", "first_name"=>"Laura", "last_name"=>"Callahan"}>] >> team.length => 4

Passing :first to find retrieves the first record found in the database. Note, though, that databases make no guarantee about which record will be first.

>> Employee.find(:first) => #__"1992-05-01", "id"=>"1", "first_name"=>"Nancy", "last_name"=>"Davolio"}>

Passing :order to find is a useful way to control how the results are ordered. This call gets the employee that was hired first:

>> Employee.find(:first, :order => "hire_date") => #__"1992-04-01", "id"=>"3", "first_name"=>"Janet", "last_name"=>"Leverling"}>

Changing the sort order returns the employee that was hired last:

>> Employee.find(:first, :order => "hire_date desc") => #__"1994-11-15", "id"=>"9", "first_name"=>"Anne", "last_name"=>"Dodsworth"}> 

You can find all employees in the table by passing :all as the first parameter:

>> Employee.find(:all).each {|e| puts e.last_name+', '+e.first_name}  Davolio, Nancy Fuller, Andrew Leverling, Janet Peacock, Margaret Buchanan, Steven Suyama, Michael King, Robert Callahan, Laura Dodsworth, Anne

Using :all with the :conditions option adds a where clause to the SQL that Active Record uses:

>> Employee.find(:all, :conditions => "hire_date > '1992' AND first_name = 'Andrew'") => [#__"1992-08-14", "id"=>"2", "first_name"=>"Andrew", "last_name"=>"Fuller"}>]

Active Record provides a better way to do SQL construction here, though. This :conditions form produces the same results as the previous one, but is safer because parameters are automatically properly escaped and quoted before being inserted into the query:

>> Employee.find(:all, :conditions => ['hire_date > ? AND first_name = ?',     '1992', 'Andrew'])  => [#__"1992-08-14", "id"=>"2", "first_name"=>"Andrew", "last_name"=>"Fuller"}>]

This is especially important if any of your search conditions originated as user input, but is a good idea no matter where they originated.

Discussion

The three different forms of find are distinguished by their first parameter. The first takes an ID or an array of IDs. It returns a single record or an array of records (corresponding to the input parameter). If it fails to find even one of the IDs, it raises RecordNotFound. The second takes the :first symbol and retrieves a single record. If no records are found, find returns nil. The third form, using :all, retrieves all records from the corresponding table. If no records are found, find returns an empty array.

All forms of find accept an options hash as the last parameter. (It's "last" and not "second" because the :id form of find can list any number of IDs as the first parameters.) The options hash can contain any or all of the following:

:conditions

Functions as the where clause of an SQL statement

:order

Determines the order of the results of the query

:group

Groups data by column values

:limit

Specifies a maximum number of rows to retrieve

:offset

Specifies the number or records to omit in the beginning of the result set

:joins

Contains SQL fragment to join multiple tables

:include

Lists named associations on which to "left outer" join with

:select

Specifies the attributes of the objects returned

:readonly

Makes the returned objects read-only so that saving them has no effect

See Also

• Section 3.12"