Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Add a dash of Arel awesomeness to your condition hashes.
Ruby
tag: v0.3.0

Fetching latest commit…

Cannot retrieve the latest commit at this time

Failed to load latest commit information.
lib
test
vendor
.document
.gitignore
.gitmodules
LICENSE
README.rdoc
Rakefile
VERSION
meta_where.gemspec

README.rdoc

MetaWhere

MetaWhere puts the power of Arel predications (comparison methods) in your ActiveRecord condition hashes.

MetaWhere offers the ability to call any Arel predicate methods (with a few convenient aliases) 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. These are methods on attributes which cause Arel to generate different SQL on your behalf.

Examples speak louder than words, so read on.

Example usage

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

You can also use similar syntax in a conditions hash supplied to ActiveRecord::Base#find:

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

Scopes

They also work 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

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

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

But wait, there's more!

Autojoin

Normally, you have to be sure to join (or include, which will join if conditions warrant) any associations that you're including in your wheres. With MetaWhere, you can just build up your relation's conditions, and tack an autojoin anywhere in the chain. MetaWhere will check out the associations you're using in your conditions and join them automatically (if they aren't already joined).

Article.where(:comments => [:body.like % '%FIRST POST%']).autojoin

Remember that joins will return duplicate rows if your conditions don't prevent it, so you might want to tack on a uniq as well.

Intelligent hash condition 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.

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

Of course, it's even simpler with autojoin, but the general idea is that if an association with the name provided exists, MetaWhere::PredicateBuilder will build the conditions against that association's table, before falling back to a standard table name scheme. It also handles nested associations:

Article.where(
  :comments => {
    :body => 'yo',
    :moderations => [:value < 0]
  },
  :other_comments => {:body => 'hey'}
).autojoin.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'))

Contrived example, I'll admit – but I'll bet you can think of some uses for this.

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.

Thanks

A huge thank you goes to Pratik Naik (lifo) for a dicussion on #rails-contrib about a patch I'd submitted, and his take on a DSL for query conditions, which was the inspiration for this gem.

Copyright

Copyright © 2010 Ernie Miller. See LICENSE for details.

Something went wrong with that request. Please try again.