Building the SQL WHERE clause dynamically, revisited

December 9th, 2005

In Building the SQL WHERE clause dynamically in Rails, I showed how to build the WHERE clause like this: 

1
2
3
4
5
6
7
8
9
10
11

class SearchController < ApplicationController
  def search
    conditions = ['1=1']

    conditions << 'cond1 = :cond1' if params[:cond1]
    conditions << 'cond2 = :cond2' if params[:cond2]

    @results = Model.find(:all,
        :conditions => [conditions.join(' AND '), params])
  end
end
Ezra Zygmuntowicz of brainsp.at fame created a Cond module to make that even easier: 
1
2
3
4
5
6
7
8
9
10
11

class SearchController < ApplicationController
  def search
    conditions = Cond::create do
      cond1 '=', params[:cond1]
      cond2 '=', params[:cond2]
    end

    @results = Model.find(:all,
        :conditions => conditions)
  end
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 ?

  • 2 comments

Building the SQL WHERE clause dynamically in Rails

October 31st, 2005

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

class SearchController < ApplicationController
  def search
    case
    when params[:cond1] && params[:cond2]
      conditions = ['cond1 = ? AND cond2 = ?',
          params[:cond1], params[:cond2]]
    when params[:cond1]
      conditions = ['cond1 = ?',
          params[:cond1]]
    when params[:cond2]
      conditions = ['cond2 = ?',
          params[:cond2]]
    end

    @results = Model.find(:all, :conditions => conditions)
  end
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
2
3
4
5
6
7
8
9
10
11

class SearchController < ApplicationController
  def search
    conditions = ['1=1']

    conditions << 'cond1 = :cond1' if params[:cond1]
    conditions << 'cond2 = :cond2' if params[:cond2]

    @results = Model.find(:all,
        :conditions => [conditions.join(' AND '), params])
  end
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: 


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 ?

  • 3 comments

 

Search

A picture of me

I am François Beausoleil, a Ruby on Rails coder. During the day, I work on XLsuite. At night, I am interested many things. Read my biography

Tags

(3) (1) (0) (2) (1) (1) (2) (2) (1) (2) (1) (2) (1) (2) (1) (1) (1) (1) (2) (14) (1) (1) (1) (1) (2) (1) (1) (2) (0) (1) (2) (1) (3) (1) (1) (1) (1) (1) (1) (0) (3) (2) (1) (2) (2) (1) (3) (2) (8) (8) (9) (12) (1) (1) (3) (1) (1) (1) (1) (1) (1) (2) (2) (2) (1) (1) (3) (1) (3) (1) (0) (23) (1) (1) (0) (1) (1) (1) (23) (25) (1) (1) (13) (1) (1) (2) (3) (1) (1) (4) (1) (2) (3) (0) (1) (7) (3) (1) (5) (5) (2) (2) (2) (4) (6) (7) (1) (0) (1) (1) (2) (2) (1) (4) (12) (2) (1) (2) (4) (1) (1) (1) (2) (8) (2) (3) (2) (2) (1) (3) (1) (1)

Links

Projects I work on

Categories

Archives