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 Jeremy Evans, 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 @tenderlove)

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!