conditions — A Single Programmer's Blog

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] && 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 ?

A Single Programmer's Blog

by François Beausoleil

Building the SQL WHERE clause dynamically, revisited

Building the SQL WHERE clause dynamically in Rails

Search

Your Host

Top Tags

Books I read and recommend

Links

Projects I work on

Projects I worked on