Rails: Building Complex Search Filters with ActiveRecord and ez_where - Part 2

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:

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



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

rubyUser.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 :filtershash:

# Apply the :admin filter

unless filters[:admin].nil?

  condition = Caboose::EZ::Condition.new :users do

    admin == filters[:admin]


  combined_conditions << condition


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,

rubyCaboose::EZ::Condition.new :users do

:firstname ~= '%' + term + '%' # ['firstname LIKE ?', '%' + term + '%']

:level <=> (5..10) # ['level BETWEEN ? AND ?', 5, 10]

:authorised true # ['authorised = ?', true]

:expiredat < 30.days.fromnow # 'expiredat < ?', 30.days.fromnow]

:permissions = [1, 5, 8] # ['permissions IN (?), [1, 5, 8']


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:

:author => 1,

:editor => 2```

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

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


combined_conditions << condition


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

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


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