I can’t begin to say how long I’ve been looking forward to writing this post. Actually, I guess I can. About a year and 2 months, based on the date of my post about “The MetaWhere 2.0 Rewrite”. Squeel has just reached an important milestone in its life: version 1.0. Since I initially started working on it, it’s evolved in ways I didn’t even imagine.

What’s new since 0.9.5?

• Default scopes are now being maintained when creating new records with AR 3.1/3.2, fixing issue #113.
• Counts with a select containing a non-string value now work properly, fixing (longstanding — sorry!) issue #83.
• Functions and operations can now be ANDed, ORed, and NOTted, allowing for easier combination of boolean operations/functions, and fixing issue #114.
• Added a simple generator for a sample Squeel initializer, in response to the feature request in issue #104.

Thanks so much for all of the bug reports and feature requests!

So, how did it turn out?

Back when I was planning the rewrite, I promised it would be 4 things:

• Modern – Releases, bundling, running the specs — pretty much everything is much less hassle than before. Environment variables allow for easy testing against varying versions of Rails/ARel, and a rake console task makes it easy to play around with Squeel using test data from the Git repo before committing to using it in your application.
• Well-tested – 777 examples, 0 failures. Even if you take into account some of the programmatically generated specs covering each predicate type for nodes that support them, there’s still a ton of testing here, helping to protect against regressions.
• Modular – According to cloc, Squeel’s lib directory is 2,129 lines of code spread across 30 files. Splitting ORM support out into adapters has enabled seamless support of ActiveRecord versions from 3.0 – 3.2, despite some significant differences in the underlying functionality. Additionally, during development, code supporting polymorphic belongs_to joins was factored into its own standalone gem, Polyamorous.
• Powerful – I’ve made a ton of different posts about the powerful functionality exposed by Squeel before, but I’ll outline a few nifty things below. Hopefully, I’ll convince you to give Squeel a try, if you haven’t already.

Yo dawg, I heard you like features…

…so I put a feature list in Squeel’s post so it can feature its features.

Block-based DSL

Squeel queries are expressed in a block (instance_evaled by default, but customizable), allowing lots of fun syntactic sugar without monkeypatching Symbol to do it. This includes SQL operators that look like Ruby operators, SQL function calls that look just like Ruby method calls, and more.

Article.where{id + 1 == 2}
=> [#<Article id: 1, title: "...">]
 
Article.select{[id,lower(title).as(title)]}.to_sql
=> SELECT "articles"."id", lower("articles"."title") AS title FROM "articles"
 
Article.select{[id,lower(title).as(title)]}.first
=> #<Article id: 1, title: "soluta consequatur consequatur non a necessitatibus..."> 
 
Article.group{person_id}.having{id == max(id)}.to_sql
=> SELECT "articles".* FROM "articles"  GROUP BY "articles"."person_id"
   HAVING "articles"."id" = max("articles"."id")

KeyPaths

Referring to associations in queries is a pain with nested hashes. KeyPaths let you drill down several levels at once:

Person.joins{articles.comments}.
       where{articles.comments.body =~ '%blah%'}.to_sql
=> "SELECT "people".* FROM "people" 
   INNER JOIN "articles" ON "articles"."person_id" = "people"."id" 
   INNER JOIN "comments" ON "comments"."article_id" = "articles"."id" 
   WHERE "comments"."body" LIKE '%blah%'"

Sifters

Sifters are sort of like scopes, but more flexible. They’re basically little snippets of conditions that take parameters. This lets them be used successfully to search against joined associations, where a named scope might fail.

def self.title_or_body_contains(string)
  where{title.matches("%#{string}%") | body.matches("%#{string}%")}
end

That works great, but because only scopes defined on the base model are available, it breaks when you want to use those conditions in a query for all people who wrote articles that contain the string. So you convert it to a sifter:

class Article < ActiveRecord::Base
  sifter :title_or_body_contains do |string|
    title.matches("%#{string}%") | body.matches("%#{string}%")
  end
end

# OR...

def self.title_or_body_contains(string)
  # AR::Base.squeel creates a Squeel DSL block that is "unattached" to
  # where_values, select_values, etc.
  squeel{title.matches("%#{string}%") | body.matches("%#{string}%")}
end

Now you can write:

Article.where{sift :title_or_body_contains, 'awesome'}
=> SELECT "articles".* FROM "articles"  
   WHERE ((
     "articles"."title" LIKE '%awesome%' 
     OR "articles"."body" LIKE '%awesome%'
   ))

Person.joins{articles}.
       where{articles.sift(:title_or_body_contains, 'awesome')}
# => SELECT "people".* FROM "people" 
     INNER JOIN "articles" ON "articles"."person_id" = "people"."id" 
     WHERE ((
       "articles"."title" LIKE '%awesome%' 
       OR "articles"."body" LIKE '%awesome%'
     ))

Lazy evaluation of association conditions

This basically means that you can join associations to your heart’s content, without worrying about what aliases are assigned to them when constructing the query. This is handiest when you’re dealing with self-referencing associations like a Person who has_many :children and belongs_to :parent, all in the people table.

Here’s a nonsensical example, traversing back and forth across the associations:

Person.joins{children.parent.children}.
       where{
         (children.name.like 'Ernie%') |
         (children.parent.name.like 'Ernie%') |
         (children.parent.children.name.like 'Ernie%')
       }
=> SELECT "people".* FROM "people" 
   INNER JOIN "people" "children_people" ON "children_people"."parent_id" = "people"."id" 
   INNER JOIN "people" "parents_people" ON "parents_people"."id" = "children_people"."parent_id" 
   INNER JOIN "people" "children_people_2" ON "children_people_2"."parent_id" = "parents_people"."id" 
   WHERE ((("children_people"."name" LIKE 'Ernie%' 
         OR "parents_people"."name" LIKE 'Ernie%') 
         OR "children_people_2"."name" LIKE 'Ernie%'))

Columns as Values

This one was subject of some debate, but observing a few simple, common-sense guidelines gives you a way to refer to columns in the value-side of conditions that wasn’t previously available.

For instance, here’s a query that finds all children with the same name as their parent — note the tilde (~), which anchors the right-hand side of the comparison to the base model, instead of using the left-hand side’s nesting:

Person.joins{children}.where{children.name == ~name}.to_sql
=> SELECT "people".* FROM "people" 
   INNER JOIN "people" "children_people" ON "children_people"."parent_id" = "people"."id" 
   WHERE "children_people"."name" = "people"."name""

SQL Literals

Finally, in the unlikely event that you still can’t get what you’re looking for using Squeel, the DSL has a convenient escape to raw SQL via backticks (`):

Article.select{[id, `"WACKY STUFF HERE"`.as('title')]}.first
=> #<Article id: 1, title: "WACKY STUFF HERE">

Obviously, when you’re dealing with raw SQL, escaping any user-supplied input is your own job, so if you intend to provide user-supplied input to your raw SQL, please ensure you know what you’re doing. :) Still, a useful trick to have up your sleeve!

That’s it, for now!

I’m really excited to hit this milestone. If you haven’t given Squeel a try yet, because you were waiting on it to mature, now’s the time. Please give it a try and let me know what you think, and what you’d like to see next! Thanks!