Squeel

Squeel is a (Rails 3.1-only for now) rewrite of MetaWhere. It’s rapidly approaching a point where I could recommend it for daily use. Once it hits feature completion, I’ll work on backporting to Rails 3.0.x. In the meantime, please feel free to clone this repo and give it a test drive using rake console and the models in the spec/support/schema.rb file.

Getting started

In your Gemfile:

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

In an intitializer:

Squeel.configure do |config|
  # To load hash extensions (to allow for AND (&), OR (|), and NOT (-) against
  # hashes of conditions)
  config.load_core_extensions :hash

  # To load symbol extensions (for a subset of the old MetaWhere functionality,
  # via ARel predicate methods on Symbols: :name.matches, etc)
  # config.load_core_extensions :symbol

  # To load both hash and symbol extensions
  # config.load_core_extensions :hash, :symbol
end

The Squeel Query DSL

Squeel enhances the normal ActiveRecord query methods by enabling them to accept blocks. Inside a block, the Squeel query DSL can be used. Note the use of curly braces in these examples instead of parentheses. {} denotes a Squeel DSL query.

Stubs

Stubs are, for most intents and purposes, just like Symbols in a normal call to Relation#where (note the need for doubling up on the curly braces here, the first ones start the block, the second are the hash braces):

Person.where{{name => 'Ernie'}}
=> SELECT "people".* FROM "people"  WHERE "people"."name" = 'Ernie'

You normally wouldn’t bother using the DSL in this case, as a simple hash would suffice. However, stubs serve as a building block for keypaths, and keypaths are very handy.

KeyPaths

A Squeel keypath is essentially a more concise and readable alternative to a deeply nested hash. For instance, in standard ActiveRecord, you might join several associations like this to perform a query:

Person.joins(:articles => {:comments => :person})
=> SELECT "people".* FROM "people"
     INNER JOIN "articles" ON "articles"."person_id" = "people"."id"
     INNER JOIN "comments" ON "comments"."article_id" = "articles"."id"
     INNER JOIN "people" "people_comments" ON "people_comments"."id" = "comments"."person_id"

With a keypath, this would look like:

Person.joins{articles.comments.person}

A keypath can exist in the context of a hash, and is normally interpreted relative to the current level of nesting. It can be forced into an “absolute” path by anchoring it with a ~, like:

~articles.comments.person

This isn’t quite so useful in the typical hash context, but can be very useful when it comes to interpreting functions and the like. We’ll cover those later.

Joins

As you saw above, keypaths can be used as shorthand for joins. Additionally, you can specify join types (or join classes, in the case of polymorphic belongs_to joins):

Person.joins{articles.outer}
=> SELECT "people".* FROM "people"
   LEFT OUTER JOIN "articles" ON "articles"."person_id" = "people"."id"
Note.joins{notable(Person).outer}
=> SELECT "notes".* FROM "notes"
   LEFT OUTER JOIN "people"
     ON "people"."id" = "notes"."notable_id"
     AND "notes"."notable_type" = 'Person'

These can also be used inside keypaths:

Note.joins{notable(Person).articles}
=> SELECT "notes".* FROM "notes"
   INNER JOIN "people" ON "people"."id" = "notes"."notable_id"
     AND "notes"."notable_type" = 'Person'
   INNER JOIN "articles" ON "articles"."person_id" = "people"."id"

Functions

You can call SQL functions just like you would call a method in Ruby…

Person.select{coalesce(name, '<no name given>')}
=> SELECT coalesce("people"."name", '<no name given>') FROM "people"

…and you can easily give it an alias:

person = Person.select{
  coalesce(name, '<no name given>').as(name_with_default)
}.first
person.name_with_default # name or <no name given>, depending on data

Operators

You can use the standard mathematical operators (+, -, *, /)inside the Squeel DSL to specify operators in the resulting SQL, or the op method to specify another custom operator, such as the standard SQL concatenation operator, ||:

…more docs to come…