12 May 2009 · About 5 minutes read

Rails: Building Complex Search Filters with ActiveRecord and ez_where

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:

rubyfirst_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:

```sql…

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:

```rubycombined_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:

bashruby 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:

```rubyclass 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.

```rubydef 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:

```ruby# 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:

```ruby# 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

Chris Blunt
Chris Blunt @cblunt
Chris is the founder of Plymouth Software. As well as code and business, he enjoys being a Dad, swimming, and the fine art of drinking tea.