metautonomo.us

Confounding URL typists since 2007.

What’s new with Squeel?

Posted by Ernie on July 3, 2011 at 8:04 pm

Why, I’m glad you asked! It’s been a while since I’ve made any updates about Squeel — since before RailsConf, actually! A lot’s been added since then.

Improvements to in/not in with empty arrays

ARel converts an empty array to (NULL). This means that calling Model.where{attribute.in []} would generate SQL that looks for a value IN (NULL). Equality tests against NULL are undefined, so IN queries work like you might expect — none of the comparisons are true, so nothing is returned. NOT IN, however, is a different beast. Since equality tests against NULL are undefined, if you have a NULL in a NOT IN list, you will never get a row back, because while undefined isn’t true, it isn’t actually false, either. Squeel will now force an empty array to generate a true or false SQL string (1=1 or 1=0, respectively) when used with in/not in, so things behave as you would otherwise expect.

Enhancements to subquery support

ActiveRecord::Relations can be supplied as function arguments or operation operands, and they’ll behave as you might expect. You can also use subqueries in your select clause, for nifty tricks like this:

subquery = Article.where(:person_id => 1).select(:id).order{id.desc}.limit(1)
Person.where(:id => 1).select{[id, name, subquery.as('last_article_id')]}.to_sql
=> SELECT "people"."id", "people"."name", 
     (
       SELECT  "articles"."id" FROM "articles"  
       WHERE "articles"."person_id" = 1 
       ORDER BY "articles"."id" DESC LIMIT 1
     ) last_article_id
   FROM "people"  WHERE "people"."id" = 1

Shorthand for SQL literals

Want to embed some ugly, low-level SQL strings inside your Squeel DSL block? Just use backticks:

Person.where{name.like('Ernie%') & `'OH HAI SQL!'`}.to_sql
=> SELECT "people".* FROM "people"
   WHERE (("people"."name" LIKE 'Ernie%' AND 'OH HAI SQL!'))

Now with 500% more warm fuzzies

Squeel is now using TravisCI (which rocks, by the way) for testing. You can check out its latest build status here. As of this writing, specs are currently passing against MRI 1.8.7, 1.9.2, and head, Ruby Enterprise Edition, and Rubinius (1.x and 2.x). They’re passing on JRuby for me, as well, but the TravisCI folks are still sorting out an issue with their JRuby platform.

Side note: I can’t recommend TravisCI enough. If you aren’t using it, you should be.

Filed under Blog
Tagged as , , , ,
You can leave a comment, or trackback from your own site.