7.4. CRUD Operations

One thing should be clear by now. ActiveRecord really shines when it comes to simplifying the basic operations for creating, reading, updating, and deleting data. Let's systematically review the methods that are available when you want to perform CRUD (create, read, update, delete) operations.

7.4.1. Create

Because a table is represented by a model class, and its rows by instances of that class, inserting new records is as easy as creating a new model object and then saving it.

Assume that you have a model that was generated by the following command:

ruby script/generate model Book title:string author:string publisher:string
pages:integer isbn:string description:text

When you create an empty object, all of its attributes that represent columns will be set to nil:

C:projectschapter7> ruby script/generate model Book title:string author:string
publisher:string pages:integer isbn:string description:text
>> my_book = Book.new
=> #<Book id: nil, title: nil, author: nil, publisher: nil, pages: nil, isbn: nil,
description: nil, created_at: nil, updated_at: nil>

As a reminder, the >> and => tokens respectively represent the input and output within a Rails console, starting with ruby script/console.

You can use attribute writers to assign values to the columns of your record:

>> my_book.title = 'Ruby on Rails for Microsoft Developers'
=> "Ruby on Rails for Microsoft Developers"
>> my_book.author = 'Antonio Cangiano'
=> "Antonio Cangiano"
>> my_book.publisher = 'Wrox'
=> "Wrox"
>> my_book.pages = 450
=> 450
>> my_book.isbn = '978-0470374955'
=> "978-0470374955"

The object will now store those values in the respective attributes:

>> my_book
=> #<Book id: nil, title: "Ruby on Rails for Microsoft Developers", author:
 "Antonio Cangiano", publisher: "Wrox", pages: 450, isbn: "978-0470374955",
 description: nil, created_at: nil, updated_at: nil>

You can also obtain a list of attributes using the attributes method (also available as the writer method attributes=):

>> my_book.attributes
=> {"isbn"=>"978-0470374955", "updated_at"=>nil, "title"=>"Ruby on Rails for
 Microsoft Developers", "author"=>"Antonio Cangiano", "publisher"=>"Wrox",
 "description"=>nil, "pages"=>450, "created_at"=>nil}

You'll notice that no id, created_at, or updated_at values have been assigned yet. At this stage, the object contains all the data you want, but the record has yet to be saved in the database.

You can confirm this using the new_record? method:

>> my_book.new_record?
=> true

To actually create the record you'll need to save your object:

>> my_book.save
=> true

Saving returns a true value, which confirms that the procedure of storing a record in the database was successful. If you evaluate the object after you've saved it, you'll now obtain an id and the creation/update date times as well.

>> my_book
=> #<Book id: 1, title: "Ruby on Rails for Microsoft Developers", author: "Antonio
 Cangiano", publisher: "Wrox", pages: 450, isbn: "978-0470374955", description:
 nil, created_at: "2009-01-31 17:24:15", updated_at: "2009-01-31 17:24:15">

ActiveRecord offers a few alternatives to creating a variable (and manually assigning values to its attributes). The first enables you to skip the repetition of variable names by employing a block:

Book.new do |b|
  b.title = 'Professional Ruby on Rails'
  b.author = 'Noel Rappin'
  b.publisher = 'Wrox'
  b.pages = 457
  b.isbn = '978-0470223888'
  b.save
end

More importantly, ActiveRecord allows for the creation of new records by passing a hash, containing the attribute values, to the constructor. This is particularly handy when trying to create a record from a form submitted by a user. For example, imagine that params[:book] is the following hash:

{ "isbn" => "978-0470189481", "title" => "The Art of Rails", "author" => "Edward
Benson", "publisher" => "Wrox", "pages" => 309 }

At this point you can create a new record as follows:

book = Book.new(params[:book])
book.save

Or more conveniently, by instantiating the object and saving it in one step through the create method:

Book.create(params[:book])

7.4.2. Read

In the previous chapters you saw how finders are used to retrieve data. The simplest possible example you can come up with is the find method, to which an id is passed.

book = Book.find(3)

7.4.2.1. Finding by Id(s)

The most common scenario for this is to retrieve a record using the id provided by the user:

book = Book.find(params[:id])

When the id can't be found in the table, an ActiveRecord::RecordNotFound exception is raised. In a production environment, this translates into a 404, which is a sensible default. And because find accepts a list or an array of ids as well, if any of the ids is missing, the same exception will be raised. Otherwise, an array of instances is returned:

>> Book.find(1,3)
=> [#<Book id: 1, title: "Ruby on Rails for Microsoft Developers", author: "Antonio
 Cangiano", publisher: "Wrox", pages: 450, isbn: "978-0470374955", description:
 nil, created_at: "2009-01-31 17:24:15", updated_at: "2009-01-31 17:24:15">, #<Book
 id: 3, title: "The Art of Rails", author: "Edward Benson", publisher: "Wrox",
 pages: 309, isbn: "978-0470189481", description: nil, created_at: "2009-01-31
17:28:34", updated_at: "2009-01-31 17:28:34">]

7.4.2.2. :first, :last, and :all

find(:all) returns an array, and as such you can chain the method with Enumerable methods and perform nice tricks:

>> Book.find(:all).collect(&:isbn)
=> ["978-0470374955", "978-0470223888", "978-0470189481"]
>> Book.find(:all).sum(&:pages)
=> 1216

Incidentally, all three Rails books combined fail to reach the same number of pages as the most popular ASP.NET title.

Two common symbols found in any Rails application are :first and :all. Not surprisingly, the first symbol tells the finder to return only the first record in the result set as an instance of the model, and the second symbol, all the records as an array of instances:

>> Book.find(:first)
=> #<Book id: 1, title: "Ruby on Rails for Microsoft Developers", author: "Antonio
 Cangiano", publisher: "Wrox", pages: 450, isbn: "978-0470374955", description:
 nil, created_at: "2009-01-31 17:24:15", updated_at: "2009-01-31 17:24:15">
>> Book.find(:all)
=> [#<Book id: 1, title: "Ruby on Rails for Microsoft Developers", author: "Anto
nio Cangiano", publisher: "Wrox", pages: 450, isbn: "978-0470374955", descriptio
n: nil, created_at: "2009-01-31 17:24:15", updated_at: "2009-01-31 17:24:15">, #
<Book id: 2, title: "Professional Ruby on Rails", author: "Noel Rappin", publish
er: "Wrox", pages: 457, isbn: "978-0470223888", description: nil, created_at: "2
009-01-31 17:26:37", updated_at: "2009-01-31 17:26:37">, #<Book id: 3, title: "T

he Art of Rails", author: "Edward Benson", publisher: "Wrox", pages: 309, isbn:
"978-0470189481", description: nil, created_at: "2009-01-31 17:28:34", updated_a
t: "2009-01-31 17:28:34">]

A third, perhaps less common symbol, exists as well and it's :last. These three can be used in conjunction with other options as well, but starting with Rails 2.1, three handy methods have been added as shortcuts for them:

Book.first # Book.find(:first)
Book.last  # Book.find(:first, :order => "id DESC") or Book.find(:last)
Book.all   # Book.find(:all)

These three methods accept other arguments just as find does. In this chapter, they've been used interchangeably.

Finders are extremely flexible in terms of the arguments that they accept, as you can see by reading the online documentation for the methods.

7.4.2.3. :order and :group

You already encountered the :order attribute, used to specify an SQL fragment to order the results in the query. Invoking the following:

Book.find(:all, :order => "created_at DESC")

would lead to the execution of the SQL query:

SELECT * FROM "books" ORDER BY created_at DESC

Similarly, another attribute/option that's available is :group, which translates into a GROUP BY clause:

# This becomes SELECT * FROM "books" WHERE (pages > 430) GROUP BY publisher
Book.find(:all, :conditions => ["pages > ?", 430], :group => "publisher")

7.4.2.4. Adding Conditions

You can also add conditions to the finder (as you've seen before, as well):

# Equivalent to Book.all(:conditions => "pages > 430", :order => "created_at DESC")
Book.find(:all, :conditions => "pages > 430", :order => "created_at DESC")

This becomes:

SELECT * FROM "books" WHERE (pages > 430) ORDER BY created_at DESC

You don't have to reach for the logs to see the SQL queries generated by the commands you entered into the console. From the console simply run ActiveRecord::Base.logger = Logger.new(STDOUT) (as long as you aren't in production mode). You can also use the ActiveSupport::BufferedLogger, used by Rails and mentioned before in this book.

Conditions can be specified in several ways. The preceding example uses a string, but other common ways of specifying conditions are hashes and arrays.

A hash can be used as follows:

Book.find(:first, :conditions => { :author => "Antonio Cangiano", :pages => 450 })

This yields (at least on SQLite3):

SELECT * FROM "books" WHERE ("books"."author" = 'Antonio Cangiano' AND
"books"."pages" = 450) LIMIT 1

Arrays are used to substitute parameters with their values in an SQL fragment:

Book.find(:all, :conditions => ["pages > ?", 430])
Book.find(:all, :conditions => ["pages > :pages", {:pages => 430}])

If you had several parameters, the values that need to be substituted would be added to the array in the order that they appear:

Book.find(:all, :conditions => ["pages > ? AND author = ?", 430, "Antonio
Cangiano"])

Note that the substitution of the parameters with their values happens during the formulation of the query; therefore, the database will execute a normal SQL query and not, say, a parameterized query. You may wonder, then, what is the point of using parameters. Can't you just embed the variables in the SQL fragment as follows?

# Don't do this
Book.find(:all, :conditions => "pages > #{params[:pages]}")

The problem with this approach is that it poses a considerable security risk. Remember, you can't trust the user's input, and embedding the value in an SQL fragment like the preceding one would make you susceptible to SQL injection attacks. When you use the hash or the more flexible array approach, ActiveRecord takes care of escaping malicious inputs for you.

For example, imagine that a malicious user passed the value 300'; DROP TABLE books; as a parameter. If you use the correct approach:

Book.all(:conditions => ["pages > ?", params[:pages]])

you have nothing to fear:

SELECT * FROM "books" WHERE (pages > '300''; DROP TABLE books;')

The single quote after 300, which would indicate the termination of the string/value, has been properly escaped with a second one, transforming it into a regular character. The topic of security is investigated further in Chapter 11.

7.4.2.5. :limit and :offset

:limit and :offset are used to specify, respectively, the number of records that should be fetched, and how many records should be skipped. For example:

Book.find(:all, :limit => 10, :offset => 5)

This will return, if they exist, 10 rows, skipping the first 5 rows. The generated SQL is truly database-specific, and will be different for SQL Server, DB2, or Oracle, but for SQLite, it would be:

SELECT * FROM "books" LIMIT 10 OFFSET 5

If you were to follow along throughout the chapter, you'd find yourself with three records, so an empty array would be returned by the preceding Ruby instruction.

In many circumstances it makes sense to use :limit along with :offset, but :limit can also be used on its own. If, on the other hand, you specify an offset but no limit, this will have no effect on the resulting query. Please note, though, that if you try to specify an offset with no limit, yet you've passed a :first or :last symbol to the finder, then the :offset attribute will have an effect on the query because :first and :last both implicitly set a :limit => 1.

7.4.2.6. :select and :from

You should have noticed by now that all the queries generated by these finders start with SELECT *. If you want to limit the columns returned to just a few, you can use the :select option:

>> Book.first(:select => "title")
=> #<Book title: "Ruby on Rails for Microsoft Developers">

The resulting query is what you'd expect it to be:

SELECT title FROM "books" LIMIT 1

When you need more than one column, you can assign a comma-separated list as the string value for the :select key:

>> Book.first(:select => "title, author")
=> #<Book title: "Ruby on Rails for Microsoft Developers", author: "Antonio Cang
iano">

Not surprisingly, this executes:

SELECT title, author FROM "books" LIMIT 1

The :select option is particularly useful whenever you want to exclude the joined columns in a join, to avoid duplicates (for example, :select => "DISTINCT books.*"), or improve performances.

The :from option is less common, but it allows you to specify a different table name or database view for the query:

# Both execute SELECT * FROM wrox_book_catalog
Book.all(:from => "wrox_book_catalog")
Book.find(:all, :from => "wrox_book_catalog")

7.4.2.7. :include and :joins

:include is a very important option when working with associations. It enables you to perform eager loading and prevent the shudder-inducing 1+N problem, where fetching N records requires the execution of 1+N queries.

Just in case you're not familiar with this issue, and in order to show the usefulness of :include, let's see an illustrative example. Imagine that you have the following schema in the database:

create_table :books do |t|
  t.string   :title
  t.string   :publisher
  t.integer  :pages
  t.string   :isbn
  t.datetime :created_at
  t.datetime :updated_at
  t.integer  :author_id
end

create_table :authors do |t|
  t.string   :name
  t.text     :bio
  t.datetime :created_at
  t.datetime :updated_at
end

create_table :reviews do |t|
  t.string   :title
  t.text     :body
  t.string   :reviewer
  t.boolean  :published
  t.datetime :created_at
  t.datetime :updated_at
  t.integer  :book_id
end

You can assume that the Book model would have the following definition:

class Book < ActiveRecord::Base
  belongs_to :author
  has_many :reviews
end

Now imagine that you'd like to print the book title, its author, its publisher, and the title of the latest review that it received within a Ruby script (not necessarily in a Rails application). A first, perhaps naive, approach would be this:

Book.all.each do |book|
  puts "Title: #{book.title}"

puts "Author: #{book.author.name}"
  puts "Publisher: #{book.publisher}"
  puts "Latest review: #{book.reviews.last.title}"
end

The code, as it's written, would raise an exception if a book happened to have no reviews, because it would attempt to run nil.title. But this is just an example to illustrate the 1+N problem.

Book.all is equivalent to Book.find(:all) so it will only execute the query once:

SELECT * FROM "books"

Within the block, book.author.name and book.reviews.last.title will execute two queries that look like the following (the values for id and book_id can change depending on the records at hand):

SELECT * FROM "authors" WHERE ("authors"."id" = 1)
SELECT * FROM "reviews" WHERE ("reviews".book_id = 1) ORDER BY reviews.id DESC
LIMIT 1

For each iteration, two queries are executed. So if you had 100 records in the books table, in total that snippet would execute 201 queries. What's more, 100 of them would look virtually identical save for the id, which changes, and another 100 would be virtually identical except for their book_id value. This is known as the 1+N problem, and as you can imagine it's a big strike against database performances.

Technically, in this case, you have 2N+1 queries because two queries that involved associations in the loop/block were used.

How can you improve this? Thanks to :include, you can drastically better the situation. Consider the following:

Book.all(:include => :author).each do |book|
  puts "Title: #{book.title}"
  puts "Author: #{book.author.name}"
  puts "Publisher: #{book.publisher}"
  puts "Latest review: #{book.reviews.last.title}"
end

This executes two queries before you execute a statement within the block:

SELECT * FROM "books"
SELECT * FROM "authors" WHERE ("authors".id IN ('1', '2', '3',...))

The query is shortened for sake of brevity. Any id for an author that has written a book would be listed between brackets.

Within the loop, because you've included author, but not reviews, you would execute the following query 100 times:

SELECT * FROM "reviews" WHERE ("reviews".book_id = 1) ORDER BY reviews.id DESC
LIMIT

As things stand, you cut the number of queries from 201 to 102. Not bad. But :include allows you to do even better, because you're not limited to eager loading a single field. As a matter of fact, the proper way to handle this hypothetical scenario is to include both author and reviews as follows:

Book.all(:include => [:author, :reviews]).each do |book|
  puts "Title: #{book.title}"
  puts "Author: #{book.author.name}"
  puts "Publisher: #{book.publisher}"
  puts "Latest review: #{book.reviews.last.title}"
end

This way you'll execute three queries no matter how many records you have in the books table. All the data that you need is "eagerloaded" before you even enter the block.

SELECT * FROM "books"
SELECT * FROM "authors" WHERE ("authors".id IN ('1', '2', '3',...))
SELECT "reviews".* FROM "reviews" WHERE ("reviews".book_id IN (1,2,3,4,5,...))

Again, the last two queries have been shortened for the sake of brevity. Any id for an author that has written a book would be listed between brackets and any id for a book that has reviews would be listed as well.

This demonstrates the importance of eager loading associations, but you should always pay attention to SQL queries that are hidden by the abstraction layer that's provided by ActiveRecord. For example, you need to be very careful when adding :include and :conditions options at the same time. Consider the following straightforward looking line of code:

Book.find(:all, :include => [:author, :reviews],
                :conditions => ['reviews.published = ?', true])

It will generate the following SQL (SQlite3 version) for you:

SELECT "books"."id" AS t0_r0, "books"."title" AS t0_r1, "books"."publisher" AS
t0_r2, "books"."pages" AS t0_r3, "books"."isbn" AS t0_r4, "books"."created_at" AS
 t0_r5, "books"."updated_at" AS t0_r6, "books"."author_id" AS t0_r7, "authors"."id"
 AS t1_r0, "authors"."name" AS t1_r1, "authors"."bio" AS t1_r2,
"authors"."created_at" AS t1_r3, "authors"."updated_at" AS t1_r4, "reviews"."id" AS
 t2_r0, "reviews"."book_id" AS t2_r1, "reviews"."title" AS t2_r2, "reviews"."body"
AS t2_r3, "reviews"."created_at" AS t2_r4, "reviews"."updated_at" AS t2_r5,
"reviews"."reviewer" AS t2_r6 FROM "books"  LEFT OUTER JOIN "authors" ON
"authors".id = "books".author_id  LEFT OUTER JOIN "reviews" ON reviews.book_id =
books.id WHERE (reviews.published = 't')

Notice how that = 't' is strongly dependent on the way the adapter defines Boolean values. It's not uncommon for them to be implemented as 1 for true, and 0 for false.

On top of being a bit scary looking when compared to what you've seen so far, this query fails to return books that lack published reviews, because the condition reviews.published = 't' applies to the whole SQL statement. This outcome may or may not be what you wanted.

When this approach doesn't match the desired outcome, it is sensible to attack the problem from a different angle. For example, it is common to eager load an association that has conditions defined on it. At this stage don't worry about this somewhat advanced technique. You'll see an example of it later on in this chapter.

:include can also be used to include a hierarchy of associations. ActiveRecord's documentation provides an example of this:

Post.find(:all, :include => [:author, {:comments => {:author => :gravatar }}])

This will need to generate a single query that's able to load all the posts, their authors, all the comments, their authors, and gravatar pictures. Again, this is a powerful feature and it's okay to include a hierarchy of associations, which isn't too deep. And as a result, performances can improve by choosing this route. But be careful; if you try to add :conditions to something like that, you'll be facing database chocking SQL monsters in no time.

There are times where :include alone won't cut it and you may need to customize the resulting query by specifying an SQL fragment for additional join clauses. In such instances, you can use the :joins option/attribute.

7.4.2.8. :readonly and :lock

The :readonly option allows you to specify that the returned records are read-only, so that they can't be altered. Check out this console session (with the error output truncated):

>> book = Book.find(:first, :readonly => true)
=> #<Book id: 1, title: "Ruby on Rails for Microsoft Developers", author: "Antonio
 Cangiano", publisher: "Wrox", pages: 450, isbn: "978-0470374955", description:
 nil, created_at: "2009-01-31 17:24:15", updated_at: "2009-01-31 17:24:15">
>> book.title = "A different title"
=> "A different title"
>> book.save
ActiveRecord::ReadOnlyRecord: ActiveRecord::ReadOnlyRecord

The :lock option allows you to specify pessimistic locking. This is useful when dealing with concurrent transactions. For example, if a few end users attempt to increase a sales field concurrently, it is possible that you'll find yourself with an incorrect number. Using :lock => true you can lock the row, thus granting access to only one transaction at a time, while the other(s) wait for the first to be completed. This is a sample snippet:

Book.transaction do
  book = Book.find(22, :lock => true)
  book.sales += 1
  book.save!
end

The books table defined before didn't have a sales column. Consider this example to be a hypothetical scenario.

The contents of the block are wrapped in a transaction. Transactions in ActiveRecord can be instantiated by simply calling the method transaction on a model and wrapping the content of the transaction in the associated block. The book record is returned with :lock set to true when you try to add 1 to the sales field and then save the record.

The save! method is similar to save, but it raises a RecordNotSaved exception instead of returning false.

The finder :lock option provides a convenient way to perform row-level locking without having to explicitly call the lock! method on the object or the reload(:lock => true) method.

Optimistic Locking Unlike pessimistic locking, optimistic locking allows concurrent access to the same record for editing purposes, under the "optimistic" assumption that any conflicts will be minimal. The consistency of the data is achieved by adding a lock_version column (that must be defaulted to 0 and can be overwritten with set_locking_column) to the table, whose stored value for a given record is automatically incremented at each update. ActiveRecord will check that value to determine if the record has been changed since you first read it from the database, and if that's the case it won't allow a second update but rather raise ActiveRecord::StaleObjectError. At this point, it will be up to the programmer to rescue the exception and decide how to resolve the conflict. Unlike pessimistic locking, which works at the database level by appending a FOR UPDATE (or its equivalent) to the query that's generated by the finder, optimistic locking works at the application level. Please consult the documentation for the module ActiveRecord::Locking::Optimistic for further details.

Other options for finder methods exist. Consult the online documentation to learn about each of them. Also remember regardless of the options passed to a finder, it's always possible to pass an :options hash as the last parameter, to specify fragments of SQL that should be appended to the query.

7.4.2.9. Dynamic Finders

By taking advantage of Ruby's powerful metaprogramming features — method_missing in particular — ActiveRecord allows you to use dynamic finders. These methods are not defined until the moment you invoke them. They are quite handy and improve the readability of one's code.

This method invocation:

Book.find(:first, :conditions => ["title = ?", title])

can also be written as:

Book.find_by_title(title)

Rails 2.2 introduced a similar dynamic finder to retrieve the last result. Instead of writing:

Book.find(:last, :conditions => ["title = ?", title])

you can now write:

Book.find_last_by_title(title)

Similarly, consider the following:

Book.find(:all, :conditions => ["publisher = ?", publisher])

This is equivalent to:

Book.find_all_by_publisher(publisher)

Nice and concise, isn't it? But it gets even better because you can concatenate a few attributes. For example:

Book.find_all_by_title_and_publisher(title, publisher)

This is equivalent to the much more verbose:

Book.find(:all, :conditions => ["title = ? AND publisher = ?", title, publisher])

Despite being defined on the fly during the moment of their invocation, these methods are subsequently cached by Ruby and normally have a negligible amount of impact on performance.

When the Record Can't Be Found I mentioned that Book.find(params[:id]) and Book.find(params[:list_of_ids]) would raise an exception when the record(s) can't be found. This is a sensible choice in the context of Rails, because by default you access the detail page of a record by passing the id, which is handled by the show action. If you visit books/show/5 and the record with id 5 doesn't exist, you'd probably expect a 404 page, which is exactly what Rails does in production. With that in mind, this behavior is not what you'd generally want when, say, performing a search in your application. For this reason, the find method, with the exception of those two cases mentioned previously, will not raise an exception when a record can't be found. If you are retrieving a single record, nil will be returned. If this is not what you want, Rails 2.2 introduced dynamic finders in the form of find_by_<attribute>!; these raise an error when a record can't be found.

When retrieving a collection of records through the :all symbol, the all finder, or through a find_all_by_<attribute> dynamic finder, an empty array (that is, []) is returned when no records can be found. It is not uncommon to use a dynamic finder to find a record if it exists and create one if this doesn't exist. For example, Book.find_or_create_by_title("On the Road") will return the model object if the book already exists, and if not, it will create one (by actually performing the insert operation in the database) and return the object so that you can, for example, add further details and assign values to its attributes, then invoke its save or save! method.

7.4.2.10. Specifying Custom SQL Queries

The finder methods and their many options are definitely nice to work with. They make code easy to write and maintain, and don't require that you provide SQL queries by hand for most reading operations. If you are a .NET developer though, when it comes to executing SQL statements you might be used to thinking in terms of ExecuteReader, ExecuteNonQuery, and ExecuteScalar provided by, among others, the OleDbCommand and SqlCommand classes.

An initial important difference is that ActiveRecord, being opinionated software, entirely ignores the concept of stored procedures or parameterized queries.

You can work with stored procedures, and with parameterized queries, as long as you are willing to do all the work yourself, by using the database driver directly.

Despite this, perhaps discouraging bit of information, ActiveRecord still allows you to execute arbitrary SQL statements. Your weapon of choice here is find_by_sql. This method is used whenever the many options that are available for regular finders just don't cut it and you'd like to pass a full SQL statement that you've defined to the finder:

Book.find_by_sql(sql)

Being a generic method, this will return an array of model instances even if you limit the result set to a single record (for example, with LIMIT 1 for certain database engines).

The same considerations that were made in the migration section of this chapter apply here. Whenever you provide your own SQL statements, you lose the ability to be independent from the database server that's being used. Sticking to the SQL standard can help, but there's no guarantee that the application will work out of the box if you switch it from, say, MySQL to SQL Server.

Technically it's always possible to work at an even lower level of abstraction, by using the driver directly or by employing the various methods that are available through the connection object (for example, Book.connection.execute or Book.connection.insert).

7.4.2.11. Calculations

ActiveRecord provides several calculation methods such as count, sum, average, minimum, maximum, and calculate. The first five are just common shortcuts for the last one.

Consider count:

>> Book.count
=> 100

This returns the number of records within the table books. The following is the executed SQL query:

SELECT count(*) AS count_all FROM "books"

It can also be used to return the number of records whose column (passed to the method) has a value:

>> Book.count(:isbn)
=> 76

This translates into:

SELECT count("books".isbn) AS count_isbn FROM "books"

This method is often used in its simplest form, but it accepts many options including :conditions, :joins, :include, :order, :having (for example, :having => 'min(pages) > 200' to exclude short books), :group, :select, :distinct (to turn this into a distinct calculation), and of course the ever present :options for appending custom SQL fragments. Please consult the online documentation for details and further examples.

Earlier on you saw this one-liner:

Book.find(:all).sum(&:pages)

This executes SELECT * FROM books and then uses Ruby's ability to work with Enumerable to calculate the sum. That's not the most efficient way of doing it, especially if there are many records in the table that wouldn't have to be retrieved anyway. Perhaps more worrisome, if a row doesn't have a value in the pages column, this would be seen as nil by Ruby, which in turn would raise a TypeError: nil can't be coerced into Fixnum error when it tries to sum things up.

There is a better way that solves both problems; use the sum method instead:

>> Book.sum(:pages)
=> 43870

This is the actual query generated by that call:

SELECT sum("books".pages) AS sum_pages FROM "books"

As a reminder, when I mentioned that a given method translates into a certain query, I offered the SQLite3 version for illustrative purposes. This is often common among several database engines, but each adapter may end up translating queries slightly differently. For example, the :limit option is translated in a LIMIT clause in SQLite, but it's a TOP clause in SQL Server.

Unlike count, and like average, minimum, and maximum, sum requires a column name and accepts only the :options option.

Here is an example that showcases average, minimum, and maximum:

>> Book.average(:pages)
=> 438.7
>> Book.minimum(:pages)
=> 189
>> Book.maximum(:pages)
=> 1680

These three will execute the following queries:

SELECT avg("books".pages) AS avg_pages FROM "books"
SELECT min("books".pages) AS min_pages FROM "books"
SELECT max("books".pages) AS max_pages FROM "books"

calculate is a more generic method. For example, Book.count can also be written as:

Book.calculate(:count, :all)

If you were using SQL Server, you could, for instance, use it to perform calculations such as:

Book.calculate(:stdev, :pages)
Book.calculate(:var, :pages)

And this would generate SQL queries that take advantage of the STDEV and VAR functions that are available in SQL Server.

7.4.3. Update

You have several ways to update a record with ActiveRecord. The first one is to assign new values to the attributes that need to be changed:

book = Book.find_by_title("A sample book")
book.pages = 435
book.publisher = "Penguin"
book.save

This is the SQL query that's generated:

UPDATE "books" SET "pages" = 435, "publisher" = 'Penguin', "updated_at" = '2008-
08-21 23:50:03' WHERE "id" = 34

This assumes that 34 is the id of the "A sample book" book.

If there are associations, this technique can be employed as well, but you need to be careful:

book = Book.first
book.author.name = "John Doe"
book.save # Don't do this. It won't change the author name
book.author.save # Saves the author name

This only showcases how to update an associated record.

Notice how invoking save on a Book object will only execute a query if there is an attribute that's been changed. The author_id value hasn't changed in this case, so book.save will return true but won't really do anything. To update the Author object with the new name, you'll have to invoke save on book.author, yielding the following SQL query:

UPDATE "authors" SET "name" = 'John Doe', "updated_at" = '2008-08-21 23:58:36'
WHERE "id" = 1

7.4.3.1. Partial Updates and Dirty Objects

Prior to Rails 2.1, updates to ActiveRecord objects would generate SQL queries where all the columns in a row would be updated. Starting with ActiveRecord 2.1, partial updates were introduced so that only modified attributes ended up in the query (plus updated_at, which is automatically handled). This works thanks to the so-called "dirty objects" (an attribute is "dirty" when it's changed) that track unsaved attribute changes. When you call save or save! ActiveRecord updates these and only these attributes.

This example showcases a few available "dirty object" methods:

book = Book.first
book.changed?                      # false
book.title                         # "A title"
book.title = "A different title"
book.changed?                      # true
book.title_changed?                # true
book.title_was                     # "A title"
book.title_change                  # ["A title", "A different title"]
book.publisher                     # "Penguin"
book.publisher = "Wiley"
book.changed                       # ["title", "publisher"]
book.changes
# {"title"=>["A title", "A different title"], "publisher"=>["Penguin",
"Wiley"]}
book.save                          # true

changed?, changed, and changes are regular methods, whereas title_changed?, title_was, and title_change are dynamic methods that are defined when invoked with the usual method_missing trick. As such, they can be adapted to the name of the attribute that you want to track (for example, publisher_change).

The query executed by book.save then becomes:

UPDATE "books" SET "title" = 'A different title', "publisher" = 'Wiley', "updated_
at" = '2008-08-22 00:34:30' WHERE "id" = 1

Observe this session:

book = Book.first
book.title.upcase!
book.publisher << "-Blackwell"
book.changes # {}
book.save    # true, but no actual changes were made

You'd probably expect the title and publisher attributes for the record to be updated. In reality, the changes to the object have not been saved in the corresponding table. No queries whatsoever have been executed upon issuing book.save.

To solve this issue, use the *_will_change! method for attributes that are going to change through means different than a simple assignment:

>> book.title_will_change!
=> "A different title"
>> book.title.upcase!
=> "A DIFFERENT TITLE"

>> book.publisher_will_change!
=> "Wiley"
>> book.publisher << "-Blackwell"
=> "Wiley-Blackwell"
>> book.changes
=> {"title"=>["A different title", "A DIFFERENT TITLE"], "publisher"=>["Wiley",
"Wiley-Blackwell"]}
>> book.save
=> true

This time, the record is properly updated through the following query:

UPDATE "books" SET "title" = 'A DIFFERENT TITLE', "publisher" = 'Wiley-Blackwell',
 "updated_at" = '2008-08-22 01:28:15' WHERE "id" = 1

As a reminder, dates and times will obviously be different on your machine. Actually, within this chapter you will find some dates from 2008 and others from 2009 depending on whether the reported output was from the first draft of the chapter, or from its revision. Of course, the dates and times in the output are entirely irrelevant.

7.4.3.2. update_attribute and update_attributes

Updating an attribute value can also be achieved through the update_attribute method:

>> book.update_attribute(:title, "Yet another title")
=> true

Just as you'd expect, the SQL statement issued by this is:

UPDATE "books" SET "title" = 'Yet another title', "updated_at" = '2008-08-22
01:33:02' WHERE "id" = 1

Similarly, and this is much more useful, you have the update_attributes method. You encountered this method before while analyzing the code produced by the scaffold generator.

>> book.update_attributes(:title => "A newer title", :publisher => "Wrox")
=> true

The reason why this is particularly useful is that it allows you to update a record directly from the hash that's received as a parameter from an input form:

>> book.update_attributes(params[:book])
=> true

7.4.3.3. Updating Multiple Records

ActiveRecord provides you with yet another two methods to update records: update and update_all.

This is the signature for the method update:

update(id, attributes)

where id is a single id or a list of ids of objects that need to be updated; attributes is a hash of attributes to be assigned to a single object, or an array of hashes to update multiple records.

Update a single record as follows:

Book.update(3, { :title => "Ruby on Rails for Microsoft Developers", :publisher =>
 "Wrox", :isbn => "978-0470374955" })

This translates into:

UPDATE "books" SET "isbn" = '978-0470374955', "title" = 'Ruby on Rails for
Microsoft Developers', "publisher" = 'Wrox', "updated_at" = '2008-09-01 20:09:11'
 WHERE "id" = 3

If you need to update multiple records, you can do so at the same time as follows:

books = { "1" => { :publisher => "Wiley" }, "2" => { :title => "The Art of
Rails", :publisher => "Wrox" } }
Book.update(books.keys, books.values)

This translates correctly into the following two queries:

UPDATE "books" SET "publisher" = 'Wiley', "updated_at" = '2008-09-01 20:20:14'
WHERE "id" = 1

UPDATE "books" SET "title" = 'The Art of Rails', "publisher" = 'Wrox',
"updated_at"
= '2008-09-01 20:20:15' WHERE "id" = 2

The update method is not very flexible. Sure, it's going to be useful at times, but imagine that you need to update all the records in the table, or those that meet a certain condition. update will require an array of ids, as well as an array of hashes, whose cardinality depends on the number of records affected. Fear not; there is a better way without having to resort to issuing custom SQL statements: the update_all method.

This method has the following signature:

update_all(updates, conditions = nil, options = {})

where updates is a string containing a comma-separated list of column/value pairs to be set for any row that meets the conditions specified by conditions.

The following line would affect all the records in a books table:

Book.update_all("publisher = 'Wrox', returned = 0")

Again, this is not the table that we defined much earlier on in the chapter, because it didn't contain a returned field, but rather it's just an example.

Or just books that contain the word Rails:

Book.update_all("publisher = 'Wrox', returned = 0", "title LIKE '%Rails%'")

This translates into the following SQL:

UPDATE "books" SET publisher = 'Wrox', returned = 0 WHERE (title LIKE '%Rails%')

update_all returns a number indicating how many records were affected by the update.

7.4.4. Delete

Deleting a row can be accomplished through the delete class method or the destroy method (available as a class and as an instance method). delete can be used to delete both a single row or a list of rows based on their ids:

Book.delete(5)
Book.delete([3, 7, 12])

The usual returning value (it's defined by the adapter) is the number of rows that were deleted. Just like update and update_all, delete has a variant called delete_all:

Book.delete_all(["sales < ?", min_sales])

destroy is available as a class method and is used just like delete:

Book.destroy(5)
Book.destroy([3, 7, 12])

Unlike delete, destroy returns the affected record. If the record can't be located, delete returns 0, whereas destroy would have raised an ActiveRecord::RecordNotFound error.

destroy can also be invoked as an instance method:

book = Book.last
book.destroy

After deleting a row in the table, the book object is frozen to prevent accidental assignments to its attributes.

destroy_all can be used just like delete_all:

Book.destroy_all("title IS ?", nil])

destroy_all returns an empty array if no records are found to meet the condition, or an array of affected model objects otherwise. If no condition is passed to destroy_all, all the records will be destroyed.

The main difference between the two types of methods is that delete bypasses business rules defined through validations and callbacks, and directly executes a DELETE statement in the database. destroy plays by the rules, instantiating the object (if used as a class method) and respecting any callbacks or validations you may have in place. Because of this, destroy is usually a safer bet when you want to delete a single record while still enforcing any business rules you may have defined.

On the other hand, delete_all is often favored over destroy_all because it will delete all the records with a single query. destroy_all will find the records, instantiate them, and then invoke the instance method destroy on each of them. The end result is two queries for each record, and as such it's really not the way to go for larger sized tables.