Recently, I spent some time working on a patch to Rails core to allow ActiveRecord::PredicateBuilder to allow more powerful queries – that is, support for additional Arel predicates beyond Equality and In. If you’re not familiar with PredicateBuilder, that’s where the magic happens when converting the arguments you supply to ActiveRecord::Relation#where or ActiveRecord::Base#find’s :conditions hash into Arel predicates for a query. Anyway, after a brief discussion with Pratik on #rails-contrib, where he shared a bit of insight into his thoughts on tackling the same problem and encouraged me to create a plugin, I came up with a bit of a hybrid – something that I think scratches this particular itch pretty well for me: MetaWhere. Read on for how it works. [Update: If you’re arriving via Ruby5 or the Rails weblog, you might also be interested in the latest MetaWhere updates, why I forked Arel, or the MetaWhere page on GitHub. Thanks for visiting!]

MetaWhere offers the ability to call any Arel predicate methods on your model’s attributes instead of the ones normally offered by ActiveRecord’s hash parameters.

Normally, ActiveRecord interprets a conditions hash as an Arel predicate of type Equality or In, depending on whether you supply a single value or an array on the value side of the hash.

MetaWhere allows you to specify what specific Arel “predication” method you would like to use. As I’ve discussed before, these are methods on attributes which cause Arel to generate different SQL on your behalf.

Enough with the description. On to the samples!

Where

You can use MetaWhere in your usual method chain:

    Article.where(:title.matches => 'Hello%', :created_at.gt => 3.days.ago)
    => SELECT "articles".* FROM "articles" WHERE ("articles"."title" LIKE 'Hello%')
       AND ("articles"."created_at" > '2010-04-12 18:39:32.592087')

Find condition hash

Until it’s deprecated and eventually removed, MetaWhere keys can be used in your finder :conditions hash like so:

      Article.find(:all,
        :conditions => {
          :title.matches => 'Hello%',
          :created_at.gt => 3.days.ago
        }
      )

Scopes

MetaWhere also works in named scopes as you would expect:

      class Article
        scope :recent, lambda {|v| where(:created_at.gt => v.days.ago)}
      end
    
      Article.recent(14).to_sql
      => SELECT "articles".* FROM "articles"
         WHERE ("articles"."created_at" > '2010-04-01 18:54:37.030951')

Operators

Additionally, you can use certain operators as shorthand for certain Arel predication methods. These are experimental at this point and subject to change. Keep in mind that if you don’t want to enclose other conditions in {}, you should place operator conditions before any hash conditions.

      Article.where(:created_at > 100.days.ago, :title =~ 'Hi%').to_sql
      => SELECT "articles".* FROM "articles"
         WHERE ("articles"."created_at" > '2010-01-05 20:11:44.997446')
         AND ("articles"."title" LIKE 'Hi%')

Operators are:

• [] (equal)
• ^ (not equal)
• • (in array/range)
• • (not in array/range)
• =~ (matching - not a regexp but a string for SQL LIKE)
• !~ (not matching, only available under Ruby 1.9)

• (greater than)

• = (greater than or equal to)

• < (less than)
• <= (less than or equal to)

Compounds

With operators:

      Article.where((:title =~ 'Hello%') | (:title =~ 'Goodbye%')).to_sql
      => SELECT "articles".* FROM "articles" WHERE (("articles"."title" LIKE 'Hello%'
         OR "articles"."title" LIKE 'Goodbye%'))

That’s kind of annoying, since operator precedence is such that you have to put parentheses around everything. So MetaWhere also supports a substitution-inspired (String#%) syntax.

With “substitutions”:

      Article.where(:title.matches % 'Hello%' | :title.matches % 'Goodbye%').to_sql
      => SELECT "articles".* FROM "articles" WHERE (("articles"."title" LIKE 'Hello%'
         OR "articles"."title" LIKE 'Goodbye%'))

With hashes:

      Article.where(
        {:created_at.lt => Time.now} & {:created_at.gt => 1.year.ago}
      ).to_sql
      => SELECT "articles".* FROM "articles" WHERE
         ((("articles"."created_at" < '2010-04-16 00:26:30.629467')
          AND ("articles"."created_at" > '2009-04-16 00:26:30.629526')))

With both hashes and substitutions:

      Article.where(
        :title.matches % 'Hello%' &
        {:created_at.lt => Time.now, :created_at.gt => 1.year.ago}
      ).to_sql
      => SELECT "articles".* FROM  "articles" WHERE (("articles"."title" LIKE 'Hello%' AND
         ("articles"."created_at" < '2010-04-16 01:04:38.023615' AND
          "articles"."created_at" > '2009-04-16 01:04:38.023720')))

With insanity complex combinations*:

    Article.joins(:comments).where(
      {:title => 'Greetings'} |
      (
        (
          :created_at.gt % 21.days.ago &
          :created_at.lt % 7.days.ago
        ) &
        :body.matches % '%from the past%'
      ) &
      {:comments => [:body =~ '%first post!%']}
    ).to_sql
    => SELECT "articles".*
       FROM "articles"
         INNER JOIN "comments"
         ON "comments"."article_id" = "articles"."id"
       WHERE 
       ((
         "articles"."title" = 'Greetings'
         OR
         (
           (
             (
               "articles"."created_at" > '2010-03-26 05:57:57.924258'
               AND "articles"."created_at" < '2010-04-09 05:57:57.924984'
             )
             AND "articles"."body" LIKE '%from the past%'
           )
           AND "comments"."body" LIKE '%first post!%'
         )
       )) 

My Arel fork

If you encounter any strange behavior, try adding the following to your Gemfile:

    gem 'arel', :git => "git://github.com/ernie/arel.git"

This fork has some updates that have not yet been committed upstream, though I hope they will be. I think I covered all of the differences that break functionality with workarounds in MetaWhere, but I don’t really use the main arel fork in my day-to-day development so I can’t be sure.

Two things you will definitely not get without my fork (at the time of this writing) are the :notmatches (NOT LIKE) and :notin (NOT IN (val1, val2)) predications.

A final word

Anyway, I think that about does it, for now. Please feel free to post your suggestions/opinions/flames below. I’ll be working on this a bit in my spare time for at least the near future, and I’m anxious to know what the Rails community thinks about it. So go do a gem install meta_where and give it a spin!

(*) Formatting added for clarity. I said you could do this, not that you should. :)