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:

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

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: , , and so on. Let’s see what that looks like:

ruby-1.9.1-p378 > a.where('title like ?', '%welcome%').
    where('body like ?', '%greetings%').where_values
=> ["title like '%welcome%'", "body like '%greetings%'"]

Hmm. Just an array of SQL fragments. Not very interesting. What if we use hash conditions instead?

All right now, wasn’t that fun? Let’s try something else!

ruby-1.9.1-p378 > a.where(:title => 'welcome', :body => 'greetings').where_values
=> [#,
    ="welcome">, #
    =<Attribute body>, ="greetings">]

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:

ruby-1.9.1-p378 > a.where(:title => ['welcome', 'greetings'],
    :created_at => 3.days.ago .. Time.now).where_values
=> [#,
    =["welcome", "greetings"]>, #
    =<Attribute created_at>,
    =Thu, 25 Mar 2010 17:02:42 EDT -04:00..2010-03-28 17:02:42 -0400>]
 
ruby-1.9.1-p378 > a.where(:title => ['welcome', 'greetings'],
    :created_at => 3.days.ago .. Time.now).to_sql
=> "SELECT     \"articles\".* FROM       \"articles\"
    WHERE     (\"articles\".\"title\" IN ('welcome', 'greetings'))
    AND (\"articles\".\"created_at\" BETWEEN '2010-03-25 21:03:28.210309'
         AND '2010-03-28 21:03:28.210583')"

Both of these result in Arel::Predicates::In in the 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:

      .uniq.each do |where|
        next if where.blank?
 
        case where
        when Arel::SqlLiteral
          arel = arel.where(where)
        else
          sql = where.is_a?(String) ? where : where.to_sql
          arel = arel.where(Arel::SqlLiteral.new("(#{sql})"))
        end
      end

This is cool. We’re building an Arel relation up using those 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 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!

class Awesome < Arel::Predicates::Predicate
  def initialize(arel_attribute)
     = arel_attribute
  end
 
  def to_sql(formatter = nil)
    .eq('AWESOME!!!').to_sql
  end
end
 
ruby-1.9.1-p378 > a.where(Awesome.new(a.table[:title])).where_values
=> [#>]
 
ruby-1.9.1-p378 > a.where(Awesome.new(a.table[:title])).to_sql
=> "SELECT     \"articles\".* FROM       \"articles\"
    WHERE     (\"articles\".\"title\" = 'AWESOME!!!')"
 
ruby-1.9.1-p378 > a.where(
    Awesome.new(a.table[:title]).
    or(a.table[:body].matches('%AWESOME!!!%'))).to_sql
=> "SELECT     \"articles\".* FROM       \"articles\"
    WHERE     ((\"articles\".\"title\" = 'AWESOME!!!'
    OR \"articles\".\"body\" LIKE '%AWESOME!!!%'))"

Obviously the example above is pointless, but this has a lot of potential to become a worthwhile companion to named scopes for encapsulation of reusable query logic.

Now, go forth and make awesome stuff with this knowledge. :)

• >