squirrel

Squirrel is an enhancement for ActiveRecord’s find method that allows programmers to query the database using a more Rubyish syntax.

Usage

The basic idea behind Squirrel is that if you want to get all the Posts containing the word “Ruby” then the best way to specify that would be body.contains? 'Ruby'. Squirrel adds a block syntax to ActiveRecord::Base#find that allows for much more Ruby-ish syntax than the hash that gets passed in by default. For example, here’s a quick rundown of what does what (with the generated SQL in the comments):

User.find(:all) do
  first_name == "Jon"         # first_name = 'Jon'
  email =~ "%@thoughtbot.com" # email LIKE '%@thoughtbot.com'
  created_at >= 4.years.ago   # created_at >= '2004-06-27 10:34:22'
  awesomeness <=> (1..10)     # awesomeness BETWEEN 1 AND 10
  banned_since == nil         # banned_since IS NULL
end

All of the operators you’d expect are available: ==, <, >, <=, >=, <=>, =~. There are also a number of convenience methods, like .nil? and .contains?. You can also use .not to negate your condition—this is stackable with all the operators.

User.find(:all) do
  email.contains? "thoughtbot"    # email LIKE "%thoughtbot%" 
  first_name.not.nil?             # NOT(first_name IS NULL)
end

For grouping, Squirrel lets you pass blocks to any and all (which, incidentally, can also have .not attached) which will group their contents logically joined by “OR” and “AND”, respectively.

Song.find(:all) do
  any do
    artist == "The Books" 
    name.contains? "Exciter" 
    all do
      rating <=> (2..4)
      length > 30
    end
  end
end

The above query will search for all Songs that are by the band “The Books” or is a version of “Exciter” or has a rating between 2 and 4 and is longer than 30 seconds. The grouping blocks work just like parentheses in your SQL would.

Associations

One of the parts of queries that Squirrel makes really easy is associations. You can just chain your associations exactly like you would expect to be able to.

Post.find(:all) do
  user.first_name.contains? "Jon" 
end

Whatever the association is named in your model is how it will be referenced in Squirrel, even for has_many. You can even use these associations for finding things that aren’t there. For example, you can find all of the Posts on your hypothetical blog that don’t have Comments like so:

Post.find(:all) do
comments.id.nil?
end

Associations can be arbitrarily deep. As long as it can be expressed as a chain of associations from the model you start to the model you finish on, it can be done in Squirrel.

Other Specifications

Squirrel lets you specify other parts of your SQL query as well. You can order your results with order_by, to which you can either pass a column (for example order_by created_at) or a raw string (for things like RANDOM, which can’t easily be expressed in another way). For descending sorts, you can use .desc (which is an alias for .not, but looks better—for example order_by created_at.desc).

You can also limit and offset your query with, not surprisingly, limit and offset. However, even easier than that is the paginate method, which takes a hash containing :page and :per_page keys (:per_page defaults to 25 if it’s left out). The result set that comes back when you use paginate is completely compatible with will_paginate.

Advanced Search Example

Squirrel excels at making advanced searches easy and readable. Use Ruby any blocks to group together SQL OR options and and blocks to group SQL ANDs. Here’s an example extracted for a search for apartments:

def self.search(budget = '', query = '', options = {})
  find :all do
    paginate options
    all do
      status == "available" 
      price < 1000           if budget == "0-1000" 
      price <=> (1000..1500) if budget == "1000-1500" 
      price <=> (1500..2000) if budget == "1500-2000" 
      price <=> (2000..2500) if budget == "2000-2500" 
      price <=> (2500..3000) if budget == "2500-3000" 
      price > 3000           if budget == "3000+" 
      any do
        unless query.blank?
          public_location.contains? query
          public_location.contains? query
          description.contains? query
        end
      end
    end
    order_by price
  end
end