GIANT ROBOTS SMASHING INTO OTHER GIANT ROBOTS

Squirrel - Natural Looking Queries for Rails

Posted by Jon Yurek

Sep 29

I’ve never liked how you query the database in ActiveRecord. Sure it works, but so does writing straight SQL. Neither seem very integrated into the framework. So after thinking about it, I figured I’d do it one better and make something a little more Rubyish looking. So I made Squirrel.

1
2
3
4
5
6

posts = Post.find do
    user.email =~ "%thoughtbot%"
    tags.name === %w( ruby rails )
    created_on <=> [ 2.weeks.ago, 1.week.ago ]
end

I’ve seen various plugins for making queries nicer, but none looked like Ruby, because even in the best of them, they still used what I consider to be overly-awkward nested hashes to get relationships. Squirrel automatically gets the relationships and builds them as you use them. It supports all the normal associations, and it does it in a much friendlier way.

You reference associations by whatever name you gave to it in the has_many, belongs_to, etc. You can then access all the columns and relationships on that model. It is rather specific, though, and will raise errors if you misspell your relationships or don’t pluralize right.

And you simply reference columns by their normal names and use any of ==, ===, <=>, =~, >=, <=, >, and < on them pretty much like you’d expect to be able to (before you ask, yes, I cribbed the syntax from ez_where, since it makes sense). It handles nil values in == with a quick trip to IS NULL and it handles negation of conditions through the unary -.

It doesn’t yet do all the fancy stuff I’d like it to, like adding aggregation columns, limiting, or even OR joins and grouping, but it’s still much better looking than normal ActiveRecord::Base#find queries, I think. It does have some fancy stuff like order_by and placeholders, though. Here’s an example with both:

1
2
3
4
5

query = User.find do
    company.name = cname?
    order_by created_on
end

When you do that, instead of an array of results, it hands you back Squirrel’s Query object, which is the base of all its querying (imagine that). From there, you call find on it and pass a hash of the names of the placeholders you specified. No surprises there. (Also, you can get a query object for inspecting the SQL by passing :query to find, like so: query = User.find(:query) {id == 2})

1
2
3
4
5
6
7
8
9
10

users = query.find(:cname => "thoughtbot")
</code></pre>

Now, what's tricky is that you could pass any syntax-changing value to that and you'd get the right SQL in the executed query.

<filter:code lang="ruby">
query.find :cname => "thoughtbot"              # =>   company.name = "thoughtbot"
query.find :cname => nil                       # =>   company.name IS NULL
query.find :cname => ["Google", "37 Signals"]  # =>   company.name IN ("Google", "37 Signals")

So there you have it. I think it makes queries much more readable and easier to maintain.

You can obtain it with a simple piston init https://svn.thoughtbot.com/plugins/squirrel/trunk vendor/plugins/squirrel

(Updated to reflect correct URL)