Since writing this series of articles, I've begun working on a plugin that bakes the functionality into ActiveRecord. Check out the sourcecode on github: git clone git://github.com/cblunt/rails-attribute_searchable.git 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)