Rails - Oct 2005 - How to programatically build :contitions with elegance?

Hello Railers,


I wonder how to avoid a matrix of 3x3=9 IF''s when constructing  
a :conditions string for find query, when I have three independent  
sets of optional conditions.

Details: my app displays a task list. A task is associated with a  
project, a person and it also has a creation date. My Task-view has a  
set of popup-menus that trigger 3 display options:
- display tasks from projects: all, project-1, project-2, etc...
- display tasks for persons: all, person-1, person-2, etc...
- display tasks for month: all, January, February, etc...

Those popups are grouped in a form, that sends a params 
[:display_options] hash to TaskController. It works OK, the  
controller builds a DisplayOptions object from this Hash (to populate  
popups with the ''collection_select'' helper). Now I try to
implement a
query that meets the conditions set by the DisplayOptions object.

If I had only one popup, this would be simple:

     if (params[:display_options] != nil)    # we''ve got options
       @display_options = TaskHelper::DisplayOptions.new(params 
[:display_options])    # build object for view
       @tasks = Task.find( :all, :conditions => [ "project = ?",  
@display_options["project"] ] )
       ...

But I have 3 independent popups, so a query conditions can look like  
this:
     [ "project = ? and person_id = ?",  ..... ]
or like this:
     [ "person_id = ? and month = ?",  .... ]
or any other from 3x3=9 variants, depending what options are set in  
my popups. For example if ''Projects'' popup is set to
''All'' then I
don''t need to include it in the query, since tasks from any project  
should be found.

Is there some more elegant way of building such a query? Some neat  
SQL statement or some neat form of ''find'' method with
incremental
conditions builder?
My knowledge of SQL is only rudimentary (thanks to Rails!), but I''ve  
checked, that the following syntax doesn''t work (at least for MySQL):
     SELECT * FROM tasks WHERE project_id=* AND person_id=3 ...
so I can''t substitute ''*'' if popup is set to
"All".

Any ideas? TIA


-- 

Rafał Komorowski
komor-ee4meeAH724@public.gmane.org
GG: 4083718
<http://homepage.mac.com/komor/iblog/>

A quick addendum. Of course a matrix of 3 popups gives 2^3=8  
variations, not 3x3=9, sorry for lame math. Anyway, if I add a forth  
popup, then it goes crazy to 2^4=16 variations.

If my previous post was too complicated, here''s a short version: how  
to get rid of this code:

     def generate_find_conditions
       case
         when @project==0 and @month==0 and @user!=0
           ["user = ?", @user]
         when @project==0 and @month!=0 and @user==0
           ["month = ?", @month]
         when @project==0 and @month!=0 and @user!=0
           ["month = ? and user = ?", @month, @user]
         when @project!=0 and @month==0 and @user==0
           ["project = ?", @project]
         when @project!=0 and @month==0 and @user!=0
           ["project = ? and user = ?", @project, @user]
         when @project!=0 and @month!=0 and @user==0
           ["project = ? and month = ?", @project, @month]
         when @project!=0 and @month!=0 and @user!=0
           ["project = ? and month = ? and user = ?", @project,  
@month, @user]
       end
     end
     .....
     Task.find( :all, :conditions => @display_options.  
generate_find_conditions )


-- 

Rafał Komorowski
komor-ee4meeAH724@public.gmane.org
GG: 4083718
<http://homepage.mac.com/komor/iblog/>

Ugly and untested:

def generate_find_conditions
  pairs = [["user", @user],
           ["month", @month],
           ["project", @project]]

  conds = pairs.collect do |pair|
    pair[1]!=0 ? ["#{pair[0]} = ?", pair[1]] : nil
  end.compact

  cond_str = conds.collect{|c| c[0]}.join(" and ")
  cond_objs = conds.collect{|c| c[1]}
  return [cond_str, cond_objs]
end

-- 
Alex

Rafał Komorowski wrote:
> A quick addendum. Of course a matrix of 3 popups gives 2^3=8 
> variations, not 3x3=9, sorry for lame math. Anyway, if I add a forth 
> popup, then it goes crazy to 2^4=16 variations.
> 
> If my previous post was too complicated, here''s a short version:
how  to
> get rid of this code:
> 
>     def generate_find_conditions
>       case
>         when @project==0 and @month==0 and @user!=0
>           ["user = ?", @user]
>         when @project==0 and @month!=0 and @user==0
>           ["month = ?", @month]
>         when @project==0 and @month!=0 and @user!=0
>           ["month = ? and user = ?", @month, @user]
>         when @project!=0 and @month==0 and @user==0
>           ["project = ?", @project]
>         when @project!=0 and @month==0 and @user!=0
>           ["project = ? and user = ?", @project, @user]
>         when @project!=0 and @month!=0 and @user==0
>           ["project = ? and month = ?", @project, @month]
>         when @project!=0 and @month!=0 and @user!=0
>           ["project = ? and month = ? and user = ?", @project, 
@month,
> @user]
>       end
>     end
>     .....
>     Task.find( :all, :conditions => @display_options. 
> generate_find_conditions )
> 
>

On 10/31/05, Rafał Komorowski <komor@mac.com>
wrote:
> A quick addendum. Of course a matrix of 3 popups gives 2^3=8
> variations, not 3x3=9, sorry for lame math. Anyway, if I add a forth
> popup, then it goes crazy to 2^4=16 variations.
>
> If my previous post was too complicated, here's a short version: how
> to get rid of this code:
>
>      def generate_find_conditions
>        case
>          when @project==0 and @month==0 and @user!=0
>            ["user = ?", @user]
>          when @project==0 and @month!=0 and @user==0
>            ["month = ?", @month]
>          when @project==0 and @month!=0 and @user!=0
>            ["month = ? and user = ?", @month, @user]
>          when @project!=0 and @month==0 and @user==0
>            ["project = ?", @project]
>          when @project!=0 and @month==0 and @user!=0
>            ["project = ? and user = ?", @project, @user]
>          when @project!=0 and @month!=0 and @user==0
>            ["project = ? and month = ?", @project, @month]
>          when @project!=0 and @month!=0 and @user!=0
>            ["project = ? and month = ? and user = ?", @project,
> @month, @user]
>        end
>      end
class A
  def initialize(p,m,u)
    @project = p
    @month = m
    @user = u
  end

  def test
    q = []
    ary = []
    [:project, :month, :user].each {|i|
      iv = instance_variable_get("@#{i}")
      unless iv.zero?
	q << "#{i} = ?"
	ary << iv
      end
    }

    return [q.join(" and ")].concat(ary)
  end
end

a = A.new(1,2,3)
b = A.new(0,1,2)
c = A.new(1,0,2)
d = A.new(0,0,1)

p a.test
p b.test
p c.test
p d.test

martin

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Hi !

2005/10/31, Rafał Komorowski <komor@mac.com>:
> I wonder how to avoid a matrix of 3x3=9 IF's when constructing
> a :conditions string for find query, when I have three independent
> sets of optional conditions.
<snip>
> Any ideas? TIA
Yes, as a matter of fact, I do.  See this article on my blog:

Building the SQL WHERE clause dynamically in Rails [1]
http://rubyurl.com/tGQ

The meat of the article is this:
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

Hope that helps !
François

[1]
http://blog.teksol.info/articles/2005/10/31/building-the-sql-where-clause-dynamically-in-rails

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

I am interested in using the method described below to dynamically
build :conditions. As far as I can tell this would leave the door open
for sql injection attacks. Is this so? If so, does anyone have any
alternative ideas?

On 10/31/05, Francois Beausoleil <francois.beausoleil@gmail.com>
wrote:
> Building the SQL WHERE clause dynamically in Rails [1]
> http://rubyurl.com/tGQ
>
> The meat of the article is this:
> 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
> [1]
http://blog.teksol.info/articles/2005/10/31/building-the-sql-where-clause-dynamically-in-rails

Sorry for the hasty post, the following would take care of the sql
injection problems.

 :conditions => [conditions.join('' AND ''), params]

duh...

On 1/12/06, Mike Wernsing <mwernsing@gmail.com>
wrote:
> I am interested in using the method described below to dynamically
> build :conditions. As far as I can tell this would leave the door open
> for sql injection attacks. Is this so? If so, does anyone have any
> alternative ideas?
>
> On 10/31/05, Francois Beausoleil <francois.beausoleil@gmail.com>
wrote:
> > Building the SQL WHERE clause dynamically in Rails [1]
> > http://rubyurl.com/tGQ
> >
> > The meat of the article is this:
> > 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
> > [1]
http://blog.teksol.info/articles/2005/10/31/building-the-sql-where-clause-dynamically-in-rails
>