Back in October 2005, I wrote an article entitled Building the SQL WHERE Clause Dynamically. This article is obsolete and I’m replacing it with this one instead.

What do you do with a FORM that has multiple conditions? Something like this:

The way I would do it today is using named scopes:

 1 class Person < ActiveRecord::Base
 2   is_taggable
 4   named_scope :in_region,
 5     lambda {|region| {:conditions => {:region => region}}}
 6   named_scope :with_salary,
 7     lambda {|lower, upper| {:conditions =>
 8       {:salary => (lower .. upper)}}}
10   # Elided, but something similar to the above
11   named_scope :tagged_with,
12     lambda {|tags| {:conditions =>
13       ...}}
14 end

Then you need your controller to translate between what the view provides and what the model expects:

 1 class SearchesController < ApplicationController
 2   def show
 3     root = Person
 4     root = root.in_region(params[:region]) unless params[:region] =~ /all/i
 5     unless params[:salary] =~ /all/i
 6       params[:salary].gsub!("K", "000")
 7       lower, upper = params[:salary].split("-")
 8       root = root.with_salary(lower, upper)
 9     end
10     root = root.tagged_with(Tag.parse(params[:tags])) unless params[:tags].blank?
11     @people = root.paginate(:page => params[:page])
12   end
13 end

For simpler forms, the approach above would be sufficient. For more complex forms, I suggest you look at plugins that aim to provide more structure around your SQL clauses, such as:

I also suggest looking at Searching and Queries Plugins – Agile Web Development to find other plugins.


Your Host

A picture of me

I am François Beausoleil, a Ruby on Rails and Scala developer. During the day, I work on Seevibes, a platform to measure social interactions related to TV shows. At night, I am interested many things. Read my biography.

Top Tags

Books I read and recommend


Projects I work on

Projects I worked on