This article is obsolete. You may find it’s replacement at Building the SQL WHERE Clause Dynamically – updated

Over on the Rails mailing list, Rafał Komorowski asks How to programatically build :contitions with elegance?

His problem is a form with four conditions. Each may be applied, but any or all of them may be set to “All”.

The brute force way of doing this would be:

 1 class SearchController < ApplicationController
 2   def search
 3     case
 4     when params[:cond1] && params[:cond2]
 5       conditions = ['cond1 = ? AND cond2 = ?',
 6           params[:cond1], params[:cond2]]
 7     when params[:cond1]
 8       conditions = ['cond1 = ?',
 9           params[:cond1]]
10     when params[:cond2]
11       conditions = ['cond2 = ?',
12           params[:cond2]]
13     end
14 
15     @results = Model.find(:all, :conditions => conditions)
16   end
17 end

Note that in this case, you have to pay attention to the order in which you evaluate your conditions. If you put params[:cond1] before params[:cond1] && params[:cond2], and params[:cond1] is true, Ruby will only evaluate the params[:cond1] branch.

Instead, I prefer to do this:

 1 class SearchController < ApplicationController
 2   def search
 3     conditions = ['1=1']
 4 
 5     conditions << 'cond1 = :cond1' if params[:cond1]
 6     conditions << 'cond2 = :cond2' if params[:cond2]
 7 
 8     @results = Model.find(:all,
 9         :conditions => [conditions.join(' AND '), params])
10   end
11 end

Note how I initialize the conditions array: ['1=1']. This is to prevent a problem later on when Rails evaluates the conditions option to find . If we leave the array empty, Rails will append the WHERE empty condition, generating something like this:

1 SELECT * FROM models WHERE 

The database backend will complain with a syntax error.

This discussion aside, I believe this is clear and concise code. Ain’t Ruby fun ?

Search

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

Links

Projects I work on

Projects I worked on