MetaWhere

GitHub | RDocs | Issues |
Railscast

Why?
Installation
Basic Usage
Operators (optional)
Compounds
Substitutions
Associations
Order
Merging
Debugging
Contributing

Future.where :sql.not_in => ['code']

Why?

I hate SQL fragments in Rails code. Resorting to where('name LIKE ?', '%something%') is an admission of defeat. It says, “I concede to allow your rigid, 1970′s-era syntax into my elegant Ruby world of object oriented goodness.” While sometimes such concessions are necessary, they should always be a last resort, because once you move away from an abstract representation of your intended query, your query becomes more brittle. You’re now reduced to hacking about with regular expressions, string scans, and the occasional deferred variable interpolation trick (like ‘#{quoted_table_name}’) in order to maintain some semblance of flexibility.

It isn’t that I hate SQL (much). I’m perfectly capable of constructing complex queries from scratch, and did more than my fair share before coming to the Rails world. It’s that I hate the juxtaposition of SQL against Ruby. It’s like seeing your arthritic grandfather hand in hand with some hot, flexible, yoga instructor. Good for him, but sooner or later something’s going to get broken. It’s like a sentence which, tanpa alasan, perubahan ke bahasa lain, then back again.¹ It just feels wrong. It breaks the spell — the “magic” that adds to programmer joy, and for no good reason.

MetaWhere is a gem that sets out to right that wrong, and give tranquility to you, the Rails coder.

Installation

Installation is simple. In your Gemfile:

gem "meta_where"  # Last officially released gem
gem "meta_where", :git => "git://github.com/ernie/meta_where.git" # Track git repo

Or, if you prefer to install as a plugin:

rails plugin install git://github.com/ernie/meta_where.git

If you want to use the optional operator syntax for conditions, put this line somewhere in your app’s initialization (config/initializers/meta_where.rb is a good place):

MetaWhere.operator_overload!

Basic Usage

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 (optional)

Additionally, you can use certain operators as shorthand for certain Arel predication methods.

These are disabled by default, but can be enabled by calling MetaWhere.operator_overload! during your app’s initialization process.

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)
[] Function

Compounds

You can use the & and | operators to perform ands and ors within your queries.

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!%'
     )
   ))

But wait, there’s more!

Intelligent association mapping

This is one of those things I hope you find so intuitive that you forget it wasn’t built in already.

PredicateBuilder (the part of ActiveRecord responsible for turning your conditions hash into a valid SQL query) will allow you to nest conditions in order to specify a table that the conditions apply to:

  Article.joins(:comments).where(:comments => {:body => 'hey'}).to_sql
  => SELECT "articles".* FROM "articles" INNER JOIN "comments"
     ON "comments"."article_id" = "articles"."id"
     WHERE ("comments"."body" = 'hey')

This feels pretty magical at first, but the magic quickly breaks down. Consider an association named :other_comments that is just a condition against comments:

  Article.joins(:other_comments).where(
    :other_comments => {:body => 'hey'}
  ).to_sql
  => ActiveRecord::StatementInvalid: No attribute named `body`
     exists for table `other_comments`

Ick. This is because the query is being created against tables, and not against associations. You’d need to do…

  Article.joins(:other_comments).where(:comments => {:body => 'hey'})

…instead. But not with MetaWhere:

  Article.joins(:other_comments).where(
    :other_comments => {:body => 'hey'}
  ).to_sql
  => SELECT "articles".* FROM "articles"
     INNER JOIN "comments" ON "comments"."article_id" = "articles"."id"
     WHERE (("comments"."body" = 'hey'))

The general idea is that if an association with the name provided exists, MetaWhere::Builder will build the conditions against that association’s table as it’s been aliased, before falling back to assuming you’re specifying a table by name. It also handles nested associations:

  Article.where(
    :comments => {
      :body => 'yo',
      :moderations => [:value < 0]
    },
    :other_comments => {:body => 'hey'}
  ).joins(
    {:comments => :moderations},
    :other_comments
  ).to_sql
  => SELECT "articles".* FROM "articles"
     INNER JOIN "comments" ON "comments"."article_id" = "articles"."id"
     INNER JOIN "moderations" ON "moderations"."comment_id" = "comments"."id"
     INNER JOIN "comments" "other_comments_articles"
       ON "other_comments_articles"."article_id" = "articles"."id"
    WHERE (("comments"."body" = 'yo' AND "moderations"."value" < 0
      AND "other_comments_articles"."body" = 'hey'))

I’ll admit this is a contrived example, but I hope it illustrates the feature all the same. I’m sure you’ll find some uses for this. For instance, it lets you dynamically build up a conditions hash traversing your model associations, without worrying about what the eventual table aliases will be. MetaWhere will work all of that out for you.

Enhanced order clauses

If you are used to doing stuff like Article.order(‘title asc’), that will still work as you expect. However, if you pass symbols or arrays in to the order method, you can take advantage of intelligent association detection (as with “Intelligent hash condition mapping,” above) and also some convenience methods for ascending and descending sorts.

  Article.order(
    :title.desc,
    :comments => [:created_at.asc, :updated_at]
  ).joins(:comments).to_sql
  => SELECT "articles".* FROM "articles"
     INNER JOIN "comments" ON "comments"."article_id" = "articles"."id"
     ORDER BY  "articles"."title" DESC,
       "comments"."created_at" ASC, "comments"."updated_at"

Enhanced relation merges

One of the changes MetaWhere makes to ActiveRecord is to delay “compiling” the where_values into actual Arel predicates until absolutely necessary. This allows for greater flexibility and last-second inference of associations/joins from any hashes supplied. A drawback of this method is that when merging relations, ActiveRecord just assumes that the values being merged are already firmed up against a specific table name and can just be thrown together. This isn’t the case with MetaWhere, and would cause unexpected failures when merging. However, MetaWhere improves on the default ActiveRecord merge functionality in two ways. First, when called with 1 parameter, (as is always the case when using the & alias) MetaWhere will try to determine if an association exists between the two models involved in the merge. If it does, the association name will be used to construct criteria.

Additionally, to cover times when detection is impossible, or the first detected association isn’t the one you wanted, you can call merge with a second parameter, specifying the association to be used during the merge.

This merge functionality allows you to do this…

(Comment.where(:id < 7) & Article.where(:title =~ '%blah%')).to_sql
=> SELECT "comments".* FROM "comments" INNER JOIN "articles"
   ON "articles"."id" = "comments"."article_id"
   WHERE ("comments"."id" < 7) AND ("articles"."title" LIKE '%blah%')"

…or this…

 Article.where(:id < 2).merge(Comment.where(:id < 7), :lame_comments).to_sql
 => "SELECT "articles".* FROM "articles"
    INNER JOIN "comments" ON "comments"."article_id" = "articles"."id"
       AND "comments"."body" = 'first post!'
    WHERE ("articles"."id" < 2) AND ("comments"."id" < 7)"

Debug your SQL with debug_sql

The to_sql method is a handy way to show your query, but as I’ve written about before, it has its shortcomings. When your query is going to eager load some records and has conditions referencing the eager loaded tables, ActiveRecord changes its generated query, but to_sql is none the wiser. Enter debug_sql:

ruby-1.9.2-head > Article.includes(:comments).where(:comments => {:body => 'hey'}).to_sql
 => SELECT "articles".* FROM "articles" WHERE ("comments"."body" = 'hey')
#
# LIES!!!
#
ruby-1.9.2-head > Article.includes(:comments).where(:comments => {:body => 'hey'}).debug_sql
 => SELECT "articles"."id" AS t0_r0, "articles"."title" AS t0_r1,
           "articles"."body" AS t0_r2, "articles"."created_at" AS t0_r3,
           "articles"."updated_at" AS t0_r4, "articles"."lookup_id" AS t0_r5,
           "comments"."id" AS t1_r0, "comments"."article_id" AS t1_r1,
           "comments"."body" AS t1_r2, "comments"."created_at" AS t1_r3,
           "comments"."updated_at" AS t1_r4
    FROM   "articles" LEFT OUTER JOIN "comments"
           ON "comments"."article_id" = "articles"."id"
    WHERE  ("comments"."body" = 'hey')
# The (ugly) truth!

Contributing

There are several ways you can help MetaWhere continue to improve.

Use MetaWhere in your real-world projects and submit bug reports or feature suggestions.
Better yet, if you’re so inclined, fix the issue yourself and submit a patch! Or you can fork the project on GitHub and send me a pull request (please include tests!)
If you like MetaWhere, spread the word. More users == more eyes on code == more bugs getting found == more bugs getting fixed (hopefully!)
Lastly, if MetaWhere has saved you hours of development time on your latest Rails gig, and you’re feeling magnanimous, please consider making a donation to the project. I have spent hours of my personal time coding and supporting MetaWhere, and your donation would go a great way toward justifying that time spent to my loving wife. :)

¹ “for no reason, changes to another language” — with thanks to , and apologies to native speakers of Indonesian. (back)
² Formatting added for clarity. I said you could do this, not that you should. :) (back)