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:

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: 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:

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 Responses to “Building the SQL WHERE clause dynamically in Rails”

  1. mardoen Says:
    October 12th, 2007 at 10:25 PM

    Erm… how about simply checking for an empty conditions array and choosing the proper find method accordingly? The superfluous “1=1” might get optimized away by some databases, but it’s not very elegant.

  2. François Says:
    October 12th, 2007 at 10:25 PM

    Of course, that is possible. Removing a conditional in my code means one less thing to test and worry about.

    The less code I write, the better I feel. That’s all.

  3. mardoen Says:
    October 12th, 2007 at 10:25 PM

    Hm, I see. Still would have done it differently ;)

Leave a Reply

 

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

(4) (1) (1) (1) (1) (2) (1) (1) (1) (2) (2) (1) (2) (1) (3) (1) (2) (1) (1) (1) (1) (2) (14) (1) (1) (1) (1) (2) (1) (1) (2) (0) (1) (4) (1) (3) (1) (1) (1) (1) (1) (1) (0) (3) (2) (1) (2) (1) (3) (1) (5) (2) (10) (10) (11) (14) (2) (1) (3) (1) (1) (1) (1) (1) (0) (1) (2) (2) (2) (1) (1) (1) (4) (1) (3) (1) (4) (2) (2) (25) (2) (1) (1) (0) (1) (1) (1) (23) (25) (1) (1) (13) (1) (1) (1) (4) (5) (1) (1) (1) (4) (1) (2) (3) (4) (4) (1) (1) (1) (8) (3) (1) (5) (5) (2) (2) (2) (4) (8) (7) (1) (1) (1) (1) (2) (4) (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