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.

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.

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:

/app/models/product.rb

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.

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.

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:

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.

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.

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.

/app/models/product.rb

defself.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.

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.

terminal

> t = Product.arel_table

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

terminal

>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

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.

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’.

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:

ruby

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.