With the release of Rails 3, the plugins and code described below no longer works. Read the updated post on building dynamic complex queries with composed scopes.

The code for this series of articles is also available:
git clone git://github.com/cblunt/blog-complex_search_filters_with_rails.git

In the first part of this tutorial, we used the ez_where plugin to build a more complex search filter into a User model class. In this tutorial, we’ll extend the search filters with additional criteria, and in part 3 we’ll build a controller that ties all the functionality together.

Searching Email Addresses for Terms

Currently, our User class’ search method accepts a :terms key as part of its options hash that is used to filter first and last names. For searches, I prefer a single text box that searches all the text data in a model – Google style – rather than separate boxes for first name, last name, email address, etc. To make the :terms filter search email addresses, just add the highlighted line to your code:

# app/models/user.rb
unless filters[:terms].nil?
  filters[:terms].each do |term|
    term = ['%', term, '%'].join
    condition = Caboose::EZ::Condition.new :users
    condition.append ['first_name LIKE ?', term], :or
    condition.append ['last_name LIKE ?', term], :or
    condition.append ['email_address LIKE ?', term], :or # << find users by email address
    combined_conditions << condition
  end
end

You could now search for all users named Mary with an email address at company.com using:

User.search :all, :filters => { :terms => %w(mary company.com)}

Filtering Additional Criteria

For large data sets, you’ll probably need to add more granular filters, search as searching for active or inactive clients, or searching for users who are only admins. Our User.search method can be extended to do that by adding more options to the :filters hash:

# app/models/user.rb
# Apply the :admin filter
unless filters[:admin].nil?
  condition = Caboose::EZ::Condition.new :users do
    admin == filters[:admin]
  end
 
  combined_conditions << condition
end

Notice here I’ve used ez_where’s block notation to build the condition. Within the do…end, you can make use of ez_where’s ruby-like syntax for conditions. For example,

Caboose::EZ::Condition.new :users do
  :first_name ~= '%' + term + '%'   # ['first_name LIKE ?', '%' + term + '%']
  :level <=> (5..10) # ['level BETWEEN ? AND ?', 5, 10]
  :authorised == true # ['authorised = ?', true]
  :expired_at < 30.days.from_now # 'expired_at < ?', 30.days.from_now]
  :permissions === [1, 5, 8] # ['permissions IN (?), [1, 5, 8']
end

There are other operators as well (see the documentation), and you can even nest conditions within a block for complex queries. However, each of these conditions is joined with an AND clause, which is why we couldn’t use the block notation for the :terms filter.

Finally, we’ll add the :status option to our User.search:filters hash. In our User model, status is an integer representing the user’s state or level of authorisation. This could be represented in a settings hash, for example:

:normal => 0,
:author => 1,
:editor => 2

Our :status filter will take an array of states and use the SQL IN clause to filter the appropriate users:

# app/models/user.rb
# Apply the :status filter
unless filters[:status].nil?
  condition = Caboose::EZ::Condition.new :users do
    status === [*filters[:status]] # use [*obj] rather than obj.to_a as Object.to_a is depracated
  end
 
  combined_conditions << condition
end

So, we can now filter users by their status and/or admin attributes using:

User.search :all, :filters => { :admin => true, :status => 2}
User.search :first, :filters => { :admin => false, :status => [0, 2]}

The next post will show how to build a controller and search form that lets users filter perform complex searches using the new User.search method that we’ve built. In the meantime, please discuss in the comments.

Resources

Full source code for the user model (user.rb.tar.gz)

With the release of Rails 3, the plugins and code described below no longer works. Read the updated post on building dynamic complex queries with composed scopes.

The code for this series of articles is also available:
git clone git://github.com/cblunt/blog-complex_search_filters_with_rails.git

Rails’ out-of-the box ActiveRecord is great for doing simple searches, but today’s web-apps often require more complex filters that are not so easy to achieve using the basic condition-builders provided by ActiveRecord.

Recently, I’ve been experimenting with the ez_where plugin to allow complex searches to be performed on a model. Unfortunately, from what I can tell, there’s not been much development on this plugin for a while.

This is a shame, because ez_where adds some great functionality to ActiveRecord. It allows you to build up complex SQL conditions using a very Ruby-like syntax. For example, you can build a fuzzy (I)LIKE condition using:

first_name =~ ['%', search_terms, '%'].join

Ez_where’s real power, though, lies in its ability to build complex search criteria by combining conditions with both AND or OR operators. This is similar to my experience with other frameworks, notably symfony‘s depracated Propel library, and a custom ORM framework I built in PHP.

Complex conditions are built in ez_where with the Caboose::EZ::Condition class. For example, we can build the following SQL query fragment:

...
WHERE (first_name LIKE "%search_terms%" OR last_name LIKE "%search_terms%")
AND (STATUS = 1 OR is_admin = TRUE)
...

Using ez_where, you break each condition into a Condition class:

combined_conditions = Caboose::EZ::Condition.new :users
search_terms = ['%', search_terms, '%'].join
 
# Build conditions for the search terms for the users table
condition = Caboose::EZ::Condition.new :users
condition.append ['first_name LIKE ?', search_terms], :or
condition.append ['last_name LIKE ?', search_terms], :or
combined_conditions.append condition
 
# Build conditions for the status or is_admin flag
condition = Caboose::EZ::Condition.new :users
condition.append ['status = 1'], :or
condition.append [is_admin = TRUE], :or
combined_conditions.append condition
 
# Expand the combined conditions into an ActiveRecord query
User.find(:all, :conditions => combined_conditions.to_sql)

You can install the latest version of ez_where with the following command:

ruby script/plugin install http://opensvn.csie.org/ezra/rails/plugins/dev/ez_where

Build a Search Method Example

Once installed, we can add a search method to a User ActiveRecord class. Like ActiveRecord’s find method, search will take a hash of options. One of the options will be another hash called :filters, which will be used to build up a set of conditions. These conditions, along with any other options, will then be passed on to the find method.

Begin by stubbing the method:

class User < ActiveRecord::Base
  def self.search(*args)
    options = args.extract_options!
    self.find(args.first, options)
  end
end

This stub just forwards our options hash onto the normal find method, so User.search is the same as User.find.

Next, we need to add the code to build our filters – but first, it would help to know what attributes the User model has. For a dynamic method (e.g. in a plugin), we could use User.column_names; however, for now we’ll code the attributes in to search for a first and last name.

def self.search(*args)
  options = args.extract_options!
 
  # Extract filters from the options, or default to empty
  filters = options.delete(:filters) || {}
 
  # Create an empty condition clause, into which we can append filters
  combined_conditions = Caboose::EZ::Condition.new :users
 
  # Use filter terms to search by first_name OR last_name. Terms are supplied as an array of strings (e.g. ["joe", "bloggs"])
  unless filters[:terms].nil?
    filters[:terms].each do |term|
      term = ['%', term, '%'].join
 
      condition = Caboose::EZ::Condition.new :users
      condition.append ['first_name LIKE ?', term], :or
      condition.append ['last_name LIKE ?', term], :or
 
      combined_conditions << condition
    end
  end
 
  # Convert the combined set of filter conditions to a SQL fragment, and store in the options hash for User.find
  options[:conditions] = combined_conditions.to_sql
 
  self.find(args.first, options)
end

We can now use the User.search and a :filters hash to perform more complex searches for user records based on the supplied search terms, e.g:

# SELECT * FROM users WHERE (first_name LIKE '%joe%' OR last_name LIKE '%joe%')
User.search(:all, :filters => {:terms => %w(joe)})
=> [User<"Joe Bloggs">, User<"Joe Smith">]
 
# SELECT * FROM users WHERE (first_name LIKE '%joe%' OR last_name LIKE '%joe%') AND (first_name LIKE '%bloggs%' OR last_name LIKE '%bloggs%');
User.search(:all, :filters => {:terms => %w(joe bloggs)})
=> [User<"Joe Bloggs">]

Furthermore, because our code ends up using the standard User.find method, we can make use of other ActiveRecord extensions, e.g. will_paginate:

# user.rb
def self.paginate_search(*args)
  # ...
  options[:conditions] = combined_conditions.to_sql
  options[:page] ||= 1
 
  self.paginate(args.first, options)
end

In the next post, I’ll show how to add more complex criteria to the search filters, and build a search form to make use of the new method. In the meantime, if you are using ez_where or another plugin for complex search criteria, please let me know and/or discuss in the comments.

Read: Part 2