Reading and Deleting

Reading existing rows

Reading from a database involves first specifying which particular rows of data we are interested in. We’ll give Active Record some kind of criteria, and it will return objects containing data from the row(s) matching the criteria.

The most direct way of finding a row in a table is by specifying its primary key. Every model class supports the find() method, which takes one or more primary key values. If given just one primary key, it returns an object containing data for the corresponding row or throws an ActiveRecord::RecordNotFound exception. If given multiple primary key values, find() returns an array of the corresponding objects. Note that in this case, a RecordNotFound exception is raised if any of the IDs cannot be found. So, if the method returns without raising an error, the length of the resulting array will be equal to the number of IDs passed as parameters.

Often, though, we need to read in rows based on criteria other than their primary key value. Active Record provides additional methods enabling us to express more complex queries.

SQL and Active Record

To illustrate how Active Record works with SQL, pass a string to the where() method call corresponding to a SQL where clause. For example, to return a list of all orders for Dave with a payment type of po, we could use this:

pos = Order.where("name = 'Dave' and pay_type = 'po'")

The result will be an ActiveRecord::Relation object containing all the matching rows, each neatly wrapped in an Order object.

This is all fine if our condition is predefined, but how do we handle it when the name of the customer is set externally, like if it were coming from a web form? One way is to substitute the value of that variable into the condition string:

As the comment suggests, this really isn’t a good idea. Why? It leaves the database-wide open to something called a SQL injection attack, which the Ruby on Rails Guides describes in more detail. For now, take it as a given that substituting a string from an external source into a SQL statement is effectively the same as publishing our entire database to the whole online world.

Instead, the safe way to generate dynamic SQL is to let Active Record handle it. Doing this allows Active Record to create properly escaped SQL, which is immune from SQL injection attacks. Let’s see how this works.

If we pass multiple parameters to a where() call, Rails treats the first parameter as a template for the SQL to generate. Within this SQL, we can embed placeholders. These will be replaced at runtime by the values in the rest of the array.

One way of specifying placeholders is to insert one or more question marks in the SQL. The first question mark is replaced by the second element of the array, the next question mark by the third, and so on. For example, we could rewrite the previous query as this:

We can also use named placeholders. We do that by placing placeholders of the form :name into the string and by providing corresponding values in a hash where the keys correspond to the names in the query:

We can take this a step further. Because params is effectively a hash, we can simply pass it all to the condition. If we have a form that can be used to enter search criteria, we can use the hash of values returned from that form directly:

We can take this even further. If we pass just a hash as the condition, Rails generates a where clause using the hash keys as column names and the hash values as the values to match. Thus, we could have written the previous code even more succinctly:

pos = Order.where(params[:order])

Be careful with this latter form of condition, though. It takes all the ...