Rails 3: How to Dynamically Build Complex Queries with Composed Scopes

In previous posts, I wrote how to build complex search queries in Rails 2 using the ezwhere_ plugin. In Rails 3, such plugins and long-winded code is completely unnecessary thanks to the powerful ActiveRelation query interface.

In a project I'm working on, I needed to be able to build just such a complex search so that users could find a customer whose name or email matched one or several search terms. The search terms would be supplied as a space-separated string. For example, to find all the customers whose name (firstname or lastname) or email contained 'joe bloggs', the SQL fragment would look something like:

SELECT * FROM customers WHERE (customers.first_name ILIKE '%term_1%' OR customers.last_name ILIKE '%term_1%' OR customers.email ILIKE '%term_1%') AND (customers.first_name ILIKE '%term_2%' OR customers.last_name ILIKE '%term_2%' OR customers.email ILIKE '%term_2%');

Achieving this in ActiveRelation is deceptively simple, but most tutorials I've read only discuss how to build simple named scopes that can be chained together. I needed to be able to dynamically build the scope based on any number of search terms the user might enter.

Reading the API docs, I discovered ActiveRecord::Base.scoped which returns an empty scope object upon which you can build in your code. Here's an example that generates the SQL fragment above:

class Customer < ActiveRecord::Base

  scope :terms, lambda { |terms|

    return if terms.blank?

    composed_scope = self.scoped

    terms.split(' ').each do |term|

      term = '%' << term << '%' # Wrap the search term in % to achieve 'fuzzy' searching

      composed_scope = composed_scope.where('first_name ILIKE ? OR last_name ILIKE ?' OR email ILIKE ?', term, term, term)

    end

    composed_scope

  }

end

With this scope, you can perform complex searches that span multiple attributes (in this case first_name, last_name, and email) with ease. What's more, because the returned composed_scope is an ActiveRelation, you can continue the chain in your code, for example:

Customer.terms('joe bloggs').order('first_name').where('active = ?', true).all