Links

Advanced ActiveRecord 3 Queries with Arel

If you’ve been playing with Rails 3, you might not know that aside from the documented ways to construct your queries (chaining wheres with SQL fragments and hash conditions), there are some other nifty tricks waiting under the hood for you to discover.

SELECT description FROM scenarios WHERE level = 101

Let’s take a look at a few simple AR 3 query scenarios. First, the typical SQL fragment with substitutions:

As you know, you can chain calls together to create more complex queries, and ActiveRecord won’t generate the full query until it needs to:

ruby-1.9.1-p378 > a.where('title like ?', '%welcome%'). where('body like ?', '%greetings%').to_sql=> "SELECT \"articles\".* FROM \"articles\" WHERE (title like '%welcome%') AND (body like '%greetings%')"

This works because ActiveRecord::Relation is keeping track of the building blocks of your query behind the scenes in a bunch of arrays: @where_values, @having_values, and so on. Let’s see what that looks like:

Arel::Predicates::Equality, huh? Now this is much more interesting. Of course, equality conditions, while useful, aren’t nearly so handy as some other SQL operations. A few of these, as with AR 2.x, are available by passing different types of values in the conditions hash, such as Arrays and Ranges:

Both of these result in Arel::Predicates::In in the @where_values array. Arel is smart enough to handle them differently, though, generating an IN in the first case, and a BETWEEN in the second. Taking a look at activerecord/lib/active_record/relation/query_methods.rb, starting at line 123 in the current master branch from GitHub, in #build_arel:

This is cool. We’re building an Arel relation up using those @where_values assigned earlier. Notice the line where the local variable sql gets assigned. In the event that it’s already a string, as in the first case, it just gets used as is. But if it’s anything else, anything at all, to_sql is called on the object. As you might expect, Arel::Predicates respond to to_sql and generate SQL fragments as needed.

This means two very cool things.

Cool thing number one

The first is that any available Arel predicates can be plugged into the @where_values array. The simplest way to generate them is to chain off one of the predicate methods on an Arel::Attribute. You can do this like so:

ruby-1.9.1-p378 > a.where( a.table[:title].matches('%welcome%'). or(a.table[:id].in([1,2,3])). or(a.table[:created_at].gteq(3.days.ago))).to_sql=> "SELECT \"articles\".* FROM \"articles\" WHERE (((\"articles\".\"title\" LIKE '%welcome%' OR \"articles\".\"id\" IN (1, 2, 3)) OR \"articles\".\"created_at\" >= '2010-03-25 21:22:51.822008'))"ruby-1.9.1-p378 > a.where(a.table[:title].notmatches_all('%hi%', '%hello%', '%greetings%')).to_sql=> "SELECT \"articles\".* FROM \"articles\" WHERE ((\"articles\".\"title\" NOT LIKE '%hi%' AND \"articles\".\"title\" NOT LIKE '%hello%' AND \"articles\".\"title\" NOT LIKE '%greetings%'))"

Okay… to be fair, the last example was a shameless plug. As of this writing, the current official version of Arel doesn’t support negative matches (NOT LIKE) and doesn’t have the shorthand for OR/AND afforded by the _any and _all method suffixes, but my Arel fork does, and I’m hoping these updates get merged into the official project, because they’re integral to an upcoming version of MetaSearch.

Of course, if my changes don’t make it in, I could always just make use of …

Cool thing number two

This is probably obvious from the Relation code excerpt above, but any object that responds to to_sql can be used in a where!