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

In Building the SQL WHERE clause dynamically in Rails, I showed how to build the WHERE clause like 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

Ezra Zygmuntowicz of brainsp.at fame created a Cond module to make that even easier:


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

See Build AR::Base.find’s :conditions clause dynamically take one for the full source code.

Now, if that were turned into a plugin, wouldn’t that be nice ?

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] &amp;&amp; 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