Introducing Squeel!

Not too long ago, I mentioned that a rename for MetaWhere 2.0 was coming. It’s here, and the new name is Squeel. A lot has happened since my post about the rewrite a couple months ago, but before we get into that, a bit about the name…

Why Squeel?

A few reasons:

1. It’s fun to say. Go ahead. Try it. SQUEEEEEEEEEL!
2. It’s an anagram for “sequel,” which is the way most folks seem to pronounce SQL. I contend that squeel is as valid a pronunciation as sequel, since there isn’t a vowel to be found in that acronym. Or squirrel. Before you ask, I checked with , lead developer of Sequel, and he was totally cool with the name choice.
3. SQL is a pig. Squeel makes an attempt to put some lipstick on it. (this slogan inspired by )

So, what’s new?

For starters, you can now toss predicates, ANDs, ORs, etc on the value side of a conditions hash, and Squeel will know that the key must be an association name. This is useful shorthand to place multiple conditions against a single association, like this:

Person.joins{articles.comments}.
       where{{
         articles.comments => (
           id.in([1,2,3]) | body.matches('First post!%')
         )
       }}.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"."id" IN (1, 2, 3) 
     OR "comments"."body" LIKE 'First post!%'))

Operators

You can also use operators against columns as you can in regular SQL:

relation = Person.select{[id, (id + 1).as('id_plus_one')]}.
                  where('id_plus_one = 2')
relation.to_sql
=> SELECT "people"."id", "people"."id" + 1 AS id_plus_one FROM "people"
   WHERE (id_plus_one = 2)
relation.first.id
=> 1

Standard mathematical operators (+, -, *, /) are supported, but you can also use other operators with #op. For example, here’s one with the standard SQL concatenation operator:

relation = Person.select{name.op('||', '-diddly').as(flanderized_name)}
relation.to_sql
=> SELECT "people"."name" || '-diddly' AS flanderized_name FROM "people"
relation.first.flanderized_name
=> "Aric Smith-diddly"

Subqueries

A longstanding request for MetaWhere, you can now easily use subqueries as a value for an IN query. Just pass an ActiveRecord::Relation as the value:

relation = Article.where{person_id.in(
  Person.select{id}.where{name.in(['Aric Smith', 'Gladyce Kulas'])}
)}
relation.to_sql
=> SELECT "articles".* FROM "articles"
   WHERE "articles"."person_id" IN (
     SELECT "people"."id" FROM "people"  
     WHERE "people"."name" IN ('Aric Smith', 'Gladyce Kulas')
   )

That’s it for now. If you’re using edge Rails, please give it a try. Or, just clone the repo and play around in rake console. I hope to have Squeel ready for production use by the time I attend RailsConf next month. Let me know what you’d like to see!