#215 Advanced Queries in Rails 3

This episode will cover advanced queries in Rails 3. In episode 202 [watch, read] we covered the additions to ActiveRecord queries in Rails 3; here we’ll carry on from there and show you some more advanced tips.

Using Class Methods Instead of Scopes

The application that we’ll be using in this application has two models: Product and Category with a product belonging to a category. The product model has two named scopes: discontinued, which returns all of the products for which discontinued is true and price, which returns the products that are cheaper than a given price argument.

class Product < ActiveRecord::Base
  belongs_to :category
  scope :discontinued, where(:discontinued => true)
  scope :cheaper_than, lambda { |price| where("price < ?", price) }
end

In the second named scope we’re using a lambda. If you ever use one of these in a named scope you might consider using a class method instead especially if you’re passing in a large number of parameters or if the content of the scope is complex. Ours is fairly simple but we’ll turn it into a class method anyway.

class Product < ActiveRecord::Base
  belongs_to :category
  scope :discontinued, where(:discontinued => true)
  
  def self.cheaper_than(price)
    where("price < ?", price)
  end
end

The class method will behave in the same way as the scope did. We could do this in Rails 2 as well but it behaves much better in Rails 3. We can even reuse this method in another named scope. If we want to create a scope called cheap that returns the products that cost less than five pounds we can write it like this:

scope :cheap, cheaper_than(5)

There’s a potential trap here though. If we use a class method in a scope we need to define the scope in the class after the class method has been defined which means that the scope will need to be placed lower down in the class than is usual.

Using the Rails console we can see the SQL that the scope generates.

ruby-1.8.7-p249 > Product.cheap.to_sql
 => "SELECT    \"products\".* FROM      \"products\" WHERE    (price < 5)"

If we just call the scope we’ll see the list of cheap products.

>Product.cheap
 => [#<Product id: 1, name: "Top", price: 4.99, discontinued: nil, category_id: 3, created_at: "2010-05-24 21:01:59", updated_at: "2010-05-24 21:01:59">, #<Product id: 2, name: "Milk", price: 2.99, discontinued: nil, category_id: 2, created_at: "2010-05-24 21:02:38", updated_at: "2010-05-24 21:02:38">]

Associations

While we’re in the console we’ll show you another trick, one that involves using scopes through associations. As we mentioned before in our application a product belongs to a category. This means that we can use joins to return a SQL query that performs an inner join on the products and categories tables.

ruby-1.8.7-p249 > Category.joins(:products).to_sql
 => "SELECT     \"categories\".* FROM       \"categories\" INNER JOIN \"products\" ON \"products\".\"category_id\" = \"categories\".\"id\""

We want to find all of the categories that have products that match a certain scope, say all of the categories that have at least one product costing less than five pounds. There are two methods we can use to do this. We could use merge like this:

> Category.joins(:products).merge(Product.cheap)
 => [#<Category id: 3, name: "Games", created_at: "2010-05-24 21:00:57", updated_at: "2010-05-25 18:30:18">, #<Category id: 2, name: "Groceries", created_at: "2010-05-24 21:00:50", updated_at: "2010-05-25 18:30:39">]

Alternatively we can use an ampersand which is an alias to the same thing and so will return the same results.

> Category.joins(:products) & Product.cheap
 => [#<Category id: 3, name: "Games", created_at: "2010-05-24 21:00:57", updated_at: "2010-05-25 18:30:18">, #<Category id: 2, name: "Groceries", created_at: "2010-05-24 21:00:50", updated_at: "2010-05-25 18:30:39">]

Using these methods we can join any other queries even if they’re not in the same models enabling us to find all of the categories that have products that cost less than five pounds. If we call to_sql on the joined queries we’ll see than an inner join has been used and the WHERE clause from the named scope added to the end of the statement.

> (Category.joins(:products) & Product.cheap).to_sql
 => "SELECT     \"categories\".* FROM       \"categories\" INNER JOIN \"products\" ON \"products\".\"category_id\" = \"categories\".\"id\" WHERE     (price < 5)"

This can be a powerful technique when used inside a named scope definition. We can use it, for example, to create a scope in the Category model that finds the categories that have cheap products.

class Category < ActiveRecord::Base
  has_many :products
  scope :with_cheap_products, joins(:products) & Product.cheap
end

This new named scope will return the same categories as the joined query we wrote earlier.

> Category.with_cheap_products
 => [#<Category id: 3, name: "Games", created_at: "2010-05-24 21:00:57", updated_at: "2010-05-25 18:30:18">, #<Category id: 2, name: "Groceries", created_at: "2010-05-24 21:00:50", updated_at: "2010-05-25 18:30:39">]

One thing that we need to be aware of when dealing with conditions across associations is the table name. If we look at the SQL that is created when we call the named scope we can see that there is no qualifying table name in the WHERE clause.

> Category.with_cheap_products.to_sql
 => "SELECT     \"categories\".* FROM       \"categories\" INNER JOIN \"products\" ON \"products\".\"category_id\" = \"categories\".\"id\" WHERE     (price < 5)"

This could be a problem if both tables had a column called price as we’d have an ambiguous column name in the SQL query. We can remove this ambiguity by explicitly defining the table name in the Product model.

def self.cheaper_than(price) 
  where("products.price < ?", price)
end

Now there is no danger of the column name being ambiguous when it is used. You should always specify the table name when you use SQL strings in find conditions. If you’re using a hash, however, such as in the scope then there’s no need to worry about the table name as Rails will automatically put one in.

Building Records Through Named Scopes

The next thing we’ll show you is how to build records with named scopes. We have a named scope on our Product model called discontinued that finds all of the discontinued products.

> Product.discontinued
 => [#<Product id: 3, name: "Some DVD", price: 13.49, discontinued: true, category_id: 1, created_at: "2010-05-25 19:45:05", updated_at: "2010-05-25 19:45:05">]

As the named scope uses a hash we can call the build method on it to build a new product with its discontinued attribute already set to true.

> p = Product.discontinued.build
 => #<Product id: nil, name: nil, price: nil, discontinued: true, category_id: nil, created_at: nil, updated_at: nil>

The new product will be discontinued because this attribute is part of the where condition. This works in a similar way to an association in that when you call build on the association it automatically sets the foreign key that’s associated with that record. Here though we’re just assigning the attributes that are in the where condition. This is a handy thing to know if you need to build records that match a named scope.

Arel

We’ll finish off this episode by introducing Arel. Arel drives ActiveRecord queries under the bonnet. You probably won’t need to interact with it very often but if helps if you have an understanding of how it works and understand what it is capable of in case you ever need to use it.

To access Arel directly in Rails 3 you can access an arel_table so we’ll get the arel_table for the Product model in the console and assign it to a variable.

> t = Product.arel_table

This is a representation of the products table. We can access the columns in the table like this:

>t[:price]
 => <Attribute price>

We can call methods on an attribute to perform find conditions. For example, if we want to find all of the products that cost £2.99 we can run

> t[:price].eq(2.99)
 => #<Arel::Predicates::Equality:0x1040dd9f0 @operand1=<Attribute price>, @operand2=2.99>

This returns a predicate which is basically the find condition. There are other methods we can call too, such as matches which will perform a LIKE query. As with other queries we can call to_sql to see the SQL that this query will generate.

> t[:name].matches('%lore').to_sql
 => "\"products\".\"name\" LIKE '%lore'"

We can use the or method to string predicates together so we can find the products that cost £2.99 and have a name ending in ‘lore’.

t[:price].eq(2.99).or(t[:name].matches('%lore'))

This will return a combined predicate and we can look at the SQL query by calling to_sql as before.

> t[:price].eq(2.99).or(t[:name].matches('%lore')).to_sql
 => "(\"products\".\"price\" = 2.99 OR \"products\".\"name\" LIKE '%lore')"

We can pass predicates as arguments to an ActiveRecord where call. This means that we can pass the predicate we’ve created to Product.where to return all of the products whose price is £2.99 or whose name ends in ‘lore’.

>   Product.where(t[:price].eq(2.99).or(t[:name].matches('%lore')))
 => [#<Product id: 2, name: "Milk", price: 2.99, discontinued: nil, category_id: 2, created_at: "2010-05-24 21:02:38", updated_at: "2010-05-24 21:02:38">, #<Product id: 4, name: "Knight Lore", price: 2.99, discontinued: nil, category_id: nil, created_at: "2010-05-26 19:36:02", updated_at: "2010-05-26 19:36:02">]

We’ve only scratched the surface here; Arel is capable of a lot more and ActiveRecord only reveals a little of it. There are a number of plugins now available that take the power of Arel and put it into a more convenient interface such as MetaWhere. This allows you to access Arel methods such as matches and eq inside the conditions hash like this:

Product.where(:price.eq => 2.99, :name.matches => '%lore')

This gives you a lot more flexibility in how the conditions hash is defined and is well worth taking a look at if you need to perform more complex queries on your models.