Ruby on Rails Tutorials: MetaWhere Getting Started

29 September 2011

MetaWhere Getting Started

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, to install as a plugin:

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

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

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)
!~ (not matching, only available under Ruby 1.9)
> (greater than)
>= (greater than or equal to)
< (less than)
<= (less than or equal to)
(SQL functions – more on those below)

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. :)

Join type specification

You can choose whether to use an inner join (the default) or a left outer join by tacking .outer or .inner to the symbols specified in your joins() call:

Article.joins(:comments => :moderations.outer).to_sql
=> SELECT "articles".* FROM "articles"
   INNER JOIN "comments" ON "comments"."article_id" = "articles"."id"
   LEFT OUTER JOIN "moderations" ON "moderations"."comment_id" = "comments"."id"

SQL Functions

You can use SQL functions in your queries:

Manager.joins(:employees.outer).group('managers.id').
        having(:employees => (:count.func(:id) < 3))
=> SELECT "managers".* FROM "managers"
   LEFT OUTER JOIN "employees" ON "employees"."manager_id" = "managers"."id"
   GROUP BY managers.id HAVING count("employees"."id") < 3

If you enable Symbol operators, you can just use :count[:id], instead of calling func as shown above. SQL functions work in the SELECT, WHERE, and HAVING clauses, and can be aliased with as:

Manager.select('managers.*').
        select(:find_in_set[:id, '3,2,1'].as('position'))
=> SELECT managers.*, find_in_set("managers"."id",'3,2,1') AS position
   FROM "managers"

But wait, there’s more!

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

The general idea is that if an association with the name provided exists, MetaWhere 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'))

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

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

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 theorder 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"

Polymorphic belongs_to joins

Polymorphic associations provide great flexibility, but they can sometimes be a bit of a hassle when it comes to querying through a belongs_to association. First, you have to know what type you’re looking for to do a proper join, and then, you’re forced into using a string join in order to make it happen (which would prevent the use of MetaWhere intelligent condition mapping).

MetaWhere allows you to join polymorphic belongs_to associations like this:

Note.joins(:notable.type(Developer)).
     where(:notable.type(Developer) => {:name.matches => 'Ernie%'})
=> SELECT "notes".* FROM "notes"
   INNER JOIN "developers" ON "developers"."id" = "notes"."notable_id"
     AND "notes"."notable_type" = 'Developer'
   WHERE "developers"."name" LIKE 'Ernie%'

Using ActiveRecord objects as condition values

Wouldn’t it be nice if you could do something like this?

# Developer belongs_to Company
company = Company.find(123)
Developer.where(:company => company)

# Developer HABTM Projects
projects = [Project.first, Project.last]
Developer.joins(:projects).where(:projects => projects)

# Note belongs_to :notable, :polymorphic => true
dev1 = Developer.first
dev2 = Developer.last
project = Project.first
company = Company.first
Note.where(:notable => [dev1, dev2, project, company]).to_sql
=> SELECT "notes".* FROM "notes" WHERE (((("notes"."notable_id" IN (1, 8)
   AND "notes"."notable_type" = 'Developer') OR ("notes"."notable_id" = 1
   AND "notes"."notable_type" = 'Project')) OR ("notes"."notable_id" = 1
   AND "notes"."notable_type" = 'Company')))

With MetaWhere, you can.

Ruby on Rails Tutorials