4.6. Searching and Finding with Queries

The methods first and all are already quite nice, but usually you want to search for something specific with a query.
For describing queries, we create a new Rails project:
$ rails new jukebox
  [...]
$ cd jukebox 
$ rails generate model album name release_year:integer
  [...]
$ rake db:migrate
  [...]
$
For the examples uses here, use a db/seeds.rb with the following content:
Album.create(name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967)
Album.create(name: "Pet Sounds", release_year: 1966)
Album.create(name: "Revolver", release_year: 1966)
Album.create(name: "Highway 61 Revisited", release_year: 1965)
Album.create(name: "Rubber Soul", release_year: 1965)
Album.create(name: "What's Going On", release_year: 1971)
Album.create(name: "Exile on Main St.", release_year: 1972)
Album.create(name: "London Calling", release_year: 1979)
Album.create(name: "Blonde on Blonde", release_year: 1966)
Album.create(name: "The Beatles", release_year: 1968)
Then, set up the new database with rake db:setup:
$ rake db:setup
db/development.sqlite3 already exists
-- create_table("albums", {:force=>true})
   -> 0.0140s
-- initialize_schema_migrations_table()
   -> 0.0002s
-- assume_migrated_upto_version(20121114132302, ["/Users/xyz/sandbox/jukebox/db/migrate"])
   -> 0.0004s
$ 

find vs. where

In ActiveRecord, there are mainly two search methods: find and where. Obviously, the Rails beginner will ask immediately: "Which method should I use?". In preperation for Rails 4.0 you should use where. Many find related methods will be deprecated in Rails 4.0.

Tip

Please read the section called “where” and the section called “find”. I would particularly recommend the section called “Lazy Loading”, so that you can fully understand the optimizations and an important concept of where.

find

The simplest case is searching for a record via a primary key (by default, the id field in the database table). If I know the ID of an object (here: a record line), then I can search for the individual object or several objects at once via the ID:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.find(2)
  Album Load (4.1ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = ? LIMIT 1  [["id", 2]]
=> #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">
>> Album.find([1,3,7])
  Album Load (0.4ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" IN (1, 3, 7)
=> [#<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 7, name: "Exile on Main St.", release_year: 1972, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> 
If you always want to have an array as result, you also always have to pass an array as parameter:
>> Album.find(5).class
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = ? LIMIT 1  [["id", 5]]
=> Album(id: integer, name: string, release_year: integer, created_at: datetime, updated_at: datetime)
>> Album.find([5]).class
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = ? LIMIT 1  [["id", 5]]
=> Array
>> exit
$ 

Warning

The method find generates an exception if the ID you are searching for does not have a record in the database. If in doubt, you can/must use where (see the section called “where”), find_by_* (see the section called “Attribute Based find_by_*, find_last_by_* and find_all_by_* (find_by_attributes)”) or first check with the method exists? (see the section called “exists?”) if the record actually exists:[23]
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.last
  Album Load (0.1ms)  SELECT "albums".* FROM "albums" ORDER BY "albums"."id" DESC LIMIT 1
=> #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">
>> Album.find(50)
  Album Load (23.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = ? LIMIT 1  [["id", 50]]
ActiveRecord::RecordNotFound: Couldn't find Album with id=50
 from /Users/xyz/.rvm/gems/ruby-1.9.3-p327/gems/activerecord-3.2.9/lib/active_record/relation/finder_methods.rb:341:in `find_one'
 from /Users/xyz/.rvm/gems/ruby-1.9.3-p327/gems/activerecord-3.2.9/lib/active_record/relation/finder_methods.rb:312:in `find_with_ids'
 from /Users/xyz/.rvm/gems/ruby-1.9.3-p327/gems/activerecord-3.2.9/lib/active_record/relation/finder_methods.rb:107:in `find'
 from /Users/xyz/.rvm/gems/ruby-1.9.3-p327/gems/activerecord-3.2.9/lib/active_record/querying.rb:5:in `find'
 from (irb):2
 from /Users/xyz/.rvm/gems/ruby-1.9.3-p327/gems/railties-3.2.9/lib/rails/commands/console.rb:47:in `start'
 from /Users/xyz/.rvm/gems/ruby-1.9.3-p327/gems/railties-3.2.9/lib/rails/commands/console.rb:8:in `start'
 from /Users/xyz/.rvm/gems/ruby-1.9.3-p327/gems/railties-3.2.9/lib/rails/commands.rb:41:in `<top (required)>'
 from script/rails:6:in `require'
 from script/rails:6:in `<main>'
>> Album.exists?(50)
  Album Exists (0.2ms)  SELECT 1 AS one FROM "albums" WHERE "albums"."id" = 50 LIMIT 1
=> false
>> exit
$

Attribute Based find_by_*, find_last_by_* and find_all_by_* (find_by_attributes)

The attribute based find_by_* is a magical method because you can dynamically expand it (that's why these methods are also referred to as "dynamic finders"). The functionality is the same as that of the method where (see the section called “where”), but the code is easier to read (at least if there are not many parameters). You could of course argue for or against this approach. Just try it for yourself and decide which way you would like to go.
An example on the console says more than a thousand words. I am first using where and then find_by_* and find_last_by_* for the same queries:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.where(:release_year => 1966).first
  Album Load (0.1ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966 LIMIT 1
=> #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">
>> Album.find_by_release_year(1966)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966 LIMIT 1
=> #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">
>> Album.where(:release_year => 1966).last
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966 ORDER BY "albums"."id" DESC LIMIT 1
=> #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">
>> Album.find_last_by_release_year(1966)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966 ORDER BY "albums"."id" DESC LIMIT 1
=> #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">
>>

Important

In contrast to where, the method find_by_* either returns an object of the searched class (here Album) or nil. where always returns an array! If you do not watch out for this difference while programming, you will sooner or later get an error at this point.
>> Album.find_by_release_year(1972)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1972 LIMIT 1
=> #<Album id: 7, name: "Exile on Main St.", release_year: 1972, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">
>> Album.find_by_release_year(1972).class
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1972 LIMIT 1
=> Album(id: integer, name: string, release_year: integer, created_at: datetime, updated_at: datetime)
>> Album.find_by_release_year(2010)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 2010 LIMIT 1
=> nil
>> Album.find_by_release_year(2010).class
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 2010 LIMIT 1
=> NilClass
>> 
The method find_all_by_* always returns an array, just like where:
>> Album.find_by_release_year(2010).class
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 2010 LIMIT 1
=> NilClass
>> Album.find_all_by_release_year(2020).class
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 2020
=> Array
>> Album.find_all_by_release_year(1966).class
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966
=> Array
>> exit
$
Chaining Several Attributes
With the methods find_by_*, find_last_by_* and find_all_by_*, you can also chain several search attributes via and. Our current records do not really provide the best examples for this. So here I am chaining several attributes together via and, just to show you how the method works, even though the queries do not really make much sense:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.find_by_id_and_release_year(1, 1967)
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = 1 AND "albums"."release_year" = 1967 LIMIT 1
=> #<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">
>> Album.find_all_by_id_and_release_year(1, 1967)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = 1 AND "albums"."release_year" = 1967
=> [#<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> Album.find_by_id_and_name(5, 'The Beatles')
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = 5 AND "albums"."name" = 'The Beatles' LIMIT 1
=> nil
>> Album.find_all_by_id_and_name(5, 'The Beatles')
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = 5 AND "albums"."name" = 'The Beatles'
=> []
>>
find_or_create_by_*
When you are programming, you are often faced with the task of finding a particular record and create it if it does not yet exist. This can be done beautifully in one step with find_or_create_by_* (note the SQL code):
>> beatles = Album.find_or_create_by_name('The Beatles')
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."name" = 'The Beatles' LIMIT 1
=> #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">
>> ray_charles = Album.find_or_create_by_name('Crying Time')
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."name" = 'Crying Time' LIMIT 1
   (0.1ms)  begin transaction
  SQL (5.8ms)  INSERT INTO "albums" ("created_at", "name", "release_year", "updated_at") VALUES (?, ?, ?, ?)  [["created_at", Wed, 14 Nov 2012 13:37:51 UTC +00:00], ["name", "Crying Time"], ["release_year", nil], ["updated_at", Wed, 14 Nov 2012 13:37:51 UTC +00:00]]
   (2.5ms)  commit transaction
=> #<Album id: 11, name: "Crying Time", release_year: nil, created_at: "2012-11-14 13:37:51", updated_at: "2012-11-14 13:37:51">
>> 

Warning

When you are using find_or_create_by_*, you should always ensure that the record you are potentially creating is valid in itself. You can easily check this via the method valid? (see Section 4.15, “Validation”). A non-valid record will not be saved by find_or_create_by_*!
If you want to search for the album name but also specify the release_year for a potential create, then you can do this with find_or_create_by_*:
>> crying_time = Album.find_or_create_by_name('Genius Loves Company', :release_year => 2004)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."name" = 'Genius Loves Company' LIMIT 1
   (0.1ms)  begin transaction
  SQL (0.5ms)  INSERT INTO "albums" ("created_at", "name", "release_year", "updated_at") VALUES (?, ?, ?, ?)  [["created_at", Wed, 14 Nov 2012 13:38:26 UTC +00:00], ["name", "Genius Loves Company"], ["release_year", 2004], ["updated_at", Wed, 14 Nov 2012 13:38:26 UTC +00:00]]
   (2.9ms)  commit transaction
=> #<Album id: 12, name: "Genius Loves Company", release_year: 2004, created_at: "2012-11-14 13:38:26", updated_at: "2012-11-14 13:38:26">
>>
find_or_initialize_by_*
The method find_or_initialize_by_* works like find_or_create_by_*. But there is one important difference: find_or_initialize_by_* does not save a new record. You have to do this later via save. Example:
>> i_got_a_woman = Album.find_or_initialize_by_name('I Got a Woman', :release_year => 1955)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."name" = 'I Got a Woman' LIMIT 1
=> #<Album id: nil, name: "I Got a Woman", release_year: 1955, created_at: nil, updated_at: nil>
>> Album.last
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" ORDER BY "albums"."id" DESC LIMIT 1
=> #<Album id: 12, name: "Genius Loves Company", release_year: 2004, created_at: "2012-11-14 13:38:26", updated_at: "2012-11-14 13:38:26">
>> i_got_a_woman.save
   (0.1ms)  begin transaction
  SQL (0.7ms)  INSERT INTO "albums" ("created_at", "name", "release_year", "updated_at") VALUES (?, ?, ?, ?)  [["created_at", Wed, 14 Nov 2012 13:39:49 UTC +00:00], ["name", "I Got a Woman"], ["release_year", 1955], ["updated_at", Wed, 14 Nov 2012 13:39:49 UTC +00:00]]
   (1.9ms)  commit transaction
=> true
>> Album.last
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" ORDER BY "albums"."id" DESC LIMIT 1
=> #<Album id: 13, name: "I Got a Woman", release_year: 1955, created_at: "2012-11-14 13:39:49", updated_at: "2012-11-14 13:39:49">
>> 
Performance
Even if the methods where and find_all_by_* often feel the same, there are clear differences in terms of performance in some scenarios. Simply put: with where you will never go wrong, because it uses lazy loading. You will find out more on this topic in the section the section called “Lazy Loading”. Here a quick taste of it:
>> Album.where(:release_year => 1966).count
   (0.2ms)  SELECT COUNT(*) FROM "albums" WHERE "albums"."release_year" = 1966
=> 3
>> Album.find_all_by_release_year(1966).count
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966
=> 3
>> exit
$
The combination of the methods where and count creates a SQL query that leaves the calculation of the result up to the SQL database. The combination of the methods find_all_by_* and count first queries all records in the database, then saves these in an array and finally counts the item in this array. This hardly makes any difference in our mini database (0.1ms). But if you are working with a very large database, then you want to make use of the maximum performance of the SQL database and not push data around all over the place first.
As mentioned above: this topic will be discussed in more detail in the section called “Lazy Loading”.

where

With the method where, you can search for specific values in the database. Let's search for all albums from the year 1966:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.where(:release_year => 1966)
  Album Load (0.1ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966
=> [#<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> Album.where( :release_year => 1966 ).count
   (0.3ms)  SELECT COUNT(*) FROM "albums" WHERE "albums"."release_year" = 1966
=> 3
>>
You can also use where to search for ranges (see the section called “Range”):
>> Album.where( :release_year => 1960..1966 )
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1966)
=> [#<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> Album.where( :release_year => 1960..1966 ).count
   (0.3ms)  SELECT COUNT(*) FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1966)
=> 5
>>
And you can also specify several search factors simultaneously, separated by commas:
>> Album.where( :release_year => 1960..1966, :id => 1..5 )
  Album Load (0.4ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1966) AND ("albums"."id" BETWEEN 1 AND 5)
=> [#<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> Album.where( :release_year => 1960..1966, :id => 1..5 ).count
   (0.3ms)  SELECT COUNT(*) FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1966) AND ("albums"."id" BETWEEN 1 AND 5)
=> 4
>> 
Or an array of parameters:
>> Album.where( :release_year => [1966, 1968] )
  Album Load (0.4ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" IN (1966, 1968)
=> [#<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> Album.where( :release_year => [1966, 1968] ).count
   (0.3ms)  SELECT COUNT(*) FROM "albums" WHERE "albums"."release_year" IN (1966, 1968)
=> 4
>>

Important

The result of where is always an array. Even if it only contains one hit or if no hits are returned. If you are looking for the first hit, you need to combine the method where with the method first:
>> Album.where( :release_year => [1966, 1968] ).first
  Album Load (0.4ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" IN (1966, 1968) LIMIT 1
=> #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">
>> Album.where( :release_year => [1966, 1968] ).first.class
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" IN (1966, 1968) LIMIT 1
=> Album(id: integer, name: string, release_year: integer, created_at: datetime, updated_at: datetime)
>> exit
$ 

SQL Queries with where

Sometimes there is no other way and you just have to define and execute your own SQL query. In ActiveRecord, there are two different ways of doing this. One sanitizes each query before executing it and the other passes the query on to the SQL database 1 to 1 as it is. Normally, you should always use the sanitized version because otherwise you can easily fall victim to an SQL injection attack (see http://en.wikipedia.org/wiki/Sql_injection).
If you do not know much about SQL, you can safely skip this section. The SQL commands used here are not explained further.
Sanitized Queries
In this variant, all dynamic search parts are replaced by a question mark as placeholder and only listed as parameters after the SQL string.
In this example, we are searching for all albums whose name contains the string on:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.where( 'name like ?', '%on%' )
  Album Load (0.6ms)  SELECT "albums".* FROM "albums" WHERE (name like '%on%')
=> [#<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 6, name: "What's Going On", release_year: 1971, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 7, name: "Exile on Main St.", release_year: 1972, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 8, name: "London Calling", release_year: 1979, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> Album.where( 'name like ?', '%on%' ).count
   (0.3ms)  SELECT COUNT(*) FROM "albums" WHERE (name like '%on%')
=> 5
>>
Now all albums that were published from 1965 onwards:
>> Album.where( 'release_year > ?', 1964 ).count
   (0.2ms)  SELECT COUNT(*) FROM "albums" WHERE (release_year > 1964)
=> 11
>>
All albums that are more recent than 1970 and whose name contains the string on:
>> Album.where( 'name like ? AND release_year > ?', '%on%', 1970 ).count
   (0.3ms)  SELECT COUNT(*) FROM "albums" WHERE (name like '%on%' AND release_year > 1970)
=> 3
>>
If the variable applepie contains the desired string, you can search for it as follows:
>> search_string = 'ing'
=> "ing"
>> Album.where( 'name like ?', "%#{search_string}%").count
   (0.3ms)  SELECT COUNT(*) FROM "albums" WHERE (name like '%ing%')
=> 3
>> exit
$
Dangerous SQL Queries
If you really know what you are doing, you can of course also define the SQL query completely and forego the sanitizing of the query.
Let's count all albums whose name contain the string on:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.where( "name like '%on%'" ).count
   (0.1ms)  SELECT COUNT(*) FROM "albums" WHERE (name like '%on%')
=> 5
>> exit
$ 
Please only use this variation if you know exactly what you are doing and once you have familiarized yourself with the topic SQL injections (see http://en.wikipedia.org/wiki/Sql_injection).

Lazy Loading

As of Rails 3.0, ActiveRecord uses the method where and so it involves lazy loading. This is a mechanism that only carries out a database query if the program flow cannot be realised without the result of this query. Until then, the query is saved as ActiveRecord::Relation. (Incidentally, the opposite of lazy loading is referred to as eager loading.)
Let's have a look at the search for all albums of the year 1966.
Loading development environment (Rails 3.2.9)
>> Album.where(:release_year => 1966)
  Album Load (0.1ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966
=> [#<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> Album.where(:release_year => 1966).class
=> ActiveRecord::Relation
>> Album.where(:release_year => 1966).to_sql
=> "SELECT \"albums\".* FROM \"albums\"  WHERE \"albums\".\"release_year\" = 1966"
>>
But if Album.where(:release_year => 1966) is an ActiveRecord::Relation, why do we get an array as output in the console? Rails wants to make our life as developers easier and shows us automatically the result of the method all:
>> Album.where(:release_year => 1966).class
=> ActiveRecord::Relation
>> Album.where(:release_year => 1966).all.class
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966
=> Array
>>
Does it make sense in principle, but you are not sure what the point of it all is? Then let's cobble together a query where we nest several methods. In the following example, a is defined more and more closely and only at the end (when calling the method all) the database query would really be executed in a production system. With the method to_sql you can display the current SQL query.
>> a = Album.where(:release_year => 1965..1968)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1965 AND 1968)
=> [#<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> a.class
=> ActiveRecord::Relation
>> a = a.order(:release_year)
  Album Load (0.4ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1965 AND 1968) ORDER BY release_year
=> [#<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> a = a.limit(3)
  Album Load (0.4ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1965 AND 1968) ORDER BY release_year LIMIT 3
=> [#<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> exit
$ 
Automatic Optimization
One of the great advantages of lazy loading is the automatic optimization of the SQL query through ActiveRecord.
Let's take the sum of all release years of the albums that came out in the 70s. Then we sort the albums alphabetically and then calculate the sum.
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.where(:release_year => 1970..1979).sum(:release_year)
   (0.1ms)  SELECT SUM("albums"."release_year") AS sum_id FROM "albums" WHERE ("albums"."release_year" BETWEEN 1970 AND 1979)
=> 5922
>> Album.where(:release_year => 1970..1979).order(:name).sum(:release_year)
   (0.3ms)  SELECT SUM("albums"."release_year") AS sum_id FROM "albums" WHERE ("albums"."release_year" BETWEEN 1970 AND 1979)
=> 5922
>> exit
$
Logically, the result is the same for both queries. But the interesting thing is that ActiveRecord uses the same SQL code for both queries. It has detected that order is completely irrelevant for sum and therefore taken it out altogether.

Note

In case you are asking yourself why the first query took 3.1ms and the second 0.3ms: ActiveRecord cached the results of a SQL request within a web page request.

exists?

Sometimes you need to know if a specific record exists, and that's exactly what the method exists? is for. It returns the result true or false:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.first
  Album Load (0.1ms)  SELECT "albums".* FROM "albums" LIMIT 1
=> #<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">
>> Album.exists?(1)
  Album Exists (0.1ms)  SELECT 1 AS one FROM "albums" WHERE "albums"."id" = 1 LIMIT 1
=> true
>> Album.exists?(10000)
  Album Exists (0.2ms)  SELECT 1 AS one FROM "albums" WHERE "albums"."id" = 10000 LIMIT 1
=> false
>> exit
$
You can display all the available help on the method exists? via ri ActiveRecord::FinderMethods.exists?.

order and reverse_order

To sort a database query, you can use the method order. Example: all albums from the 60s, sorted by name:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.where( :release_year => 1960..1969 ).order(:name)
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969) ORDER BY name
=> [#<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>>
With the method reverse_order you can reverse an order previously defined via order:
>> Album.where(:release_year => 1960..1969).order(:name).reverse_order
  Album Load (0.4ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969) ORDER BY name DESC
=> [#<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> exit
$ 

limit

The result of any search can be limited to a certain range via the method limit.
The first 5 albums from the 60s:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.where( :release_year => 1960..1969).limit(5)
  Album Load (0.1ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969) LIMIT 5
=> [#<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>>
All albums sorted by name, then the first 5 of those:
>> Album.order(:name).limit(5)
  Album Load (0.4ms)  SELECT "albums".* FROM "albums" ORDER BY name LIMIT 5
=> [#<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 11, name: "Crying Time", release_year: nil, created_at: "2012-11-14 13:37:51", updated_at: "2012-11-14 13:37:51">, #<Album id: 7, name: "Exile on Main St.", release_year: 1972, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 12, name: "Genius Loves Company", release_year: 2004, created_at: "2012-11-14 13:38:26", updated_at: "2012-11-14 13:38:26">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> exit
$ 

offset

With the method offset, you can define the starting position of the method limit.
First, we return the first two records and then the first two records with an offset of 5:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.limit(2)
  Album Load (0.1ms)  SELECT "albums".* FROM "albums" LIMIT 2
=> [#<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> Album.limit(2).offset(5)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" LIMIT 2 OFFSET 5
=> [#<Album id: 6, name: "What's Going On", release_year: 1971, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 7, name: "Exile on Main St.", release_year: 1972, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> exit
$

group

With the method group, you can return the result of a query in grouped form.
Let's return all albums, grouped by their release year:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.group(:release_year)
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" GROUP BY release_year
=> [#<Album id: 11, name: "Crying Time", release_year: nil, created_at: "2012-11-14 13:37:51", updated_at: "2012-11-14 13:37:51">, #<Album id: 13, name: "I Got a Woman", release_year: 1955, created_at: "2012-11-14 13:39:49", updated_at: "2012-11-14 13:39:49">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 6, name: "What's Going On", release_year: 1971, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 7, name: "Exile on Main St.", release_year: 1972, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 8, name: "London Calling", release_year: 1979, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 12, name: "Genius Loves Company", release_year: 2004, created_at: "2012-11-14 13:38:26", updated_at: "2012-11-14 13:38:26">]
>> exit
$

pluck

Normally, ActiveRecord always pulls all table columns from the database and leaves it up to the programmer to later pick out the components he is interested in. But in case of large amounts of data, it can be useful and above all much quicker to define a specific database field directly for the query. You can do this via the method pluck. You want all name entries for all albums from the 60s?
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.where(:release_year => 1960..1969).pluck(:name)
   (0.2ms)  SELECT name FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
=> ["Sgt. Pepper's Lonely Hearts Club Band", "Pet Sounds", "Revolver", "Highway 61 Revisited", "Rubber Soul", "Blonde on Blonde", "The Beatles"]
>> exit
$
As result, pluck returns an array.

Calculations

average

With the method average, you can calculate the average of the values in a particular column of the table. Our data material is of course not really suited to this. But as an example, let's calculate the average release year of all albums and then the same for albums from the 60s:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.average(:release_year)
   (0.1ms)  SELECT AVG("albums"."release_year") AS avg_id FROM "albums" 
=> #<BigDecimal:7fdc4c0ee810,'0.1970333333 333333E4',27(45)>
>> Album.average(:release_year).to_s
   (0.3ms)  SELECT AVG("albums"."release_year") AS avg_id FROM "albums" 
=> "1970.333333333333"
>> Album.where( :release_year => 1960..1969 ).average(:release_year)
   (0.2ms)  SELECT AVG("albums"."release_year") AS avg_id FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
=> #<BigDecimal:7fdc499f1af0,'0.1966142857 142857E4',27(45)>
>> Album.where( :release_year => 1960..1969 ).average(:release_year).to_s
   (0.3ms)  SELECT AVG("albums"."release_year") AS avg_id FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
=> "1966.142857142857"
>> exit
$

count

The name says it all: the method count counts the number of records.
First, we return the number of all albums in the database and then the number of albums from the 60s:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.count
   (0.1ms)  SELECT COUNT(*) FROM "albums" 
=> 13
>> Album.where( :release_year => 1960..1969 ).count
   (0.2ms)  SELECT COUNT(*) FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
=> 7
>> exit
$

maximum

With the method maximum, you can output the item with the highest value within a query.
Let's look for the highest release year:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.maximum(:release_year)
   (0.1ms)  SELECT MAX("albums"."release_year") AS max_id FROM "albums" 
=> 2004
>> exit
$

minimum

With the method minimum, you can output the item with the lowest value within a query.
Let's find the lowest release year:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.minimum(:release_year)
   (0.1ms)  SELECT MIN("albums"."release_year") AS min_id FROM "albums" 
=> 1955
>> exit
$

sum

With the method sum, you can calculate the sum of all items in a specific column of the database query.
Let's find the sum of all release years:
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.sum(:release_year)
   (0.1ms)  SELECT SUM("albums"."release_year") AS sum_id FROM "albums" 
=> 23644
>> exit
$ 

SQL EXPLAIN

Most SQL databases can provide detailled information on a SQL query with the command EXPLAIN. This does not make much sense for our mini application, but if you are working with a large database one day, then EXPLAIN is a good debugging method, for example to find out where to place an index. SQL EXPLAIN can be called with the method explain (it will be displayed in prettier form if you add a puts):
$ rails console
Loading development environment (Rails 3.2.9)
>> Album.where(:release_year => 1960..1969)
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
=> [#<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">, #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-11-14 13:26:01", updated_at: "2012-11-14 13:26:01">]
>> Album.where(:release_year => 1960..1969).explain
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
  EXPLAIN (0.1ms)  EXPLAIN QUERY PLAN SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
=> "EXPLAIN for: SELECT \"albums\".* FROM \"albums\"  WHERE (\"albums\".\"release_year\" BETWEEN 1960 AND 1969)\n0|0|0|SCAN TABLE albums (~500000 rows)\n"
>> puts Album.where(:release_year => 1960..1969).explain
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
  EXPLAIN (0.1ms)  EXPLAIN QUERY PLAN SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
EXPLAIN for: SELECT "albums".* FROM "albums"  WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
0|0|0|SCAN TABLE albums (~500000 rows)
=> nil
>> exit
$ 


[23] You can also intercept an exception via rescue, but as this book is aimed at beginners, I am not going into more detail on this topic.