thr3ads.net - Rails - Building a conditions clause (for find) of multiple optional params? [Nov 2005]

If this information is useful, please help other people find it:
Share via:

CSN

2005-Nov-22 02:57 UTC

Building a conditions clause (for find) of multiple optional params?

I want to be able to find items according to various params - category_id,
member_id, type_id,
rating, etc. What I have now is something like:

if(@params[''category_id''])
  @items=Item.find(:all, :conditions=>["category_id=?",
@params[''category_id''])
elsif(@params[''category_id''] and
@params[''member_id''])
  @items=Item.find(:all, :conditions=>["category_id=? and
member_id=?", @params[''category_id''],
@params[''member_id''])
elsif ...

end

Is there a simpler way? Such as just adding to a conditions array, then joining
and passing it to
find() as :conditions?

thanks
csn



		
__________________________________ 
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com

Christer Nilsson

2005-Nov-22 06:58 UTC

head link

Re: Building a conditions clause (for find) of multiple opti

I have looked for a solution, but without success. Your solution 
explodes as 2^5=32 possibilities.

This is my suggestion. I need some advice regarding quotes and sql 
injection.

class Array
  def AddCond(query, value)
   return if value.blank?
   self << "#{query} #{Report.quote(value)}"
  end
  def AddLike(query, prefix, value, suffix)
   return if value.blank?
   self << "#{query} LIKE #{Report.quote(prefix+value+suffix)}"
  end
end

cond = ["1=1"]
cond.AddCond("category_id =",
@params[''category_id''))
cond.AddCond("member_id =", @params[''member_id''))
...
cond.AddLike("member_name", "%",
@params[''member_name''], "%")

... :conditions => cond.join(" and ") ...


-- 
Posted via http://www.ruby-forum.com/.

CSN

2005-Nov-22 08:55 UTC

head link

Re: Re: Building a conditions clause (for find) of multiple opti

I came up with:

conditions=["var1=1"]
if(@params[''member_id''])
  conditions[0] += " and member_id=?"
  conditions.push(@params[''member_id''])
end
if(@params[''category_id''])
  conditions[0] += " and category_id=?"
  conditions.push(@params[''category_id''])
end

@items=Items.find(:all, :conditions=>conditions)

It obviously needs a better way of handling the first array element and/or
figuring out whether a
condition is the first and whether to add " and ".

csn


--- Christer Nilsson
<janchrister.nilsson-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> I have looked for a solution, but without success. Your solution 
> explodes as 2^5=32 possibilities.
> 
> This is my suggestion. I need some advice regarding quotes and sql 
> injection.
> 
> class Array
>   def AddCond(query, value)
>    return if value.blank?
>    self << "#{query} #{Report.quote(value)}"
>   end
>   def AddLike(query, prefix, value, suffix)
>    return if value.blank?
>    self << "#{query} LIKE
#{Report.quote(prefix+value+suffix)}"
>   end
> end
> 
> cond = ["1=1"]
> cond.AddCond("category_id =",
@params[''category_id''))
> cond.AddCond("member_id =",
@params[''member_id''))
> ...
> cond.AddLike("member_name", "%",
@params[''member_name''], "%")
> 
> ... :conditions => cond.join(" and ") ...
> 
> 
> -- 
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 


	
		
__________________________________ 
Yahoo! Mail - PC Magazine Editors'' Choice 2005 
http://mail.yahoo.com

Peter Bohm

2005-Nov-22 10:21 UTC

head link

Re: Building a conditions clause (for find) of multiple opti

Just initialize conditions[0] with some always true statements something
like...
conditions[0] = " true " or conditions[0] = "1 = 1"

It''s also possible to use named parameters, which may be a bit more
readable - you will have one String with sql and one Hash with named
parameters

Peter

CSN wrote:> I came up with:
> 
> conditions=["var1=1"]
> if(@params[''member_id''])
>   conditions[0] += " and member_id=?"
>   conditions.push(@params[''member_id''])
> end
> if(@params[''category_id''])
>   conditions[0] += " and category_id=?"
>   conditions.push(@params[''category_id''])
> end
> 
> @items=Items.find(:all, :conditions=>conditions)
> 
> It obviously needs a better way of handling the first array element and/or
figuring out whether a
> condition is the first and whether to add " and ".
> 
> csn
> 
> 
> --- Christer Nilsson
<janchrister.nilsson-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> 
> 
>>I have looked for a solution, but without success. Your solution 
>>explodes as 2^5=32 possibilities.
>>
>>This is my suggestion. I need some advice regarding quotes and sql 
>>injection.
>>
>>class Array
>>  def AddCond(query, value)
>>   return if value.blank?
>>   self << "#{query} #{Report.quote(value)}"
>>  end
>>  def AddLike(query, prefix, value, suffix)
>>   return if value.blank?
>>   self << "#{query} LIKE
#{Report.quote(prefix+value+suffix)}"
>>  end
>>end
>>
>>cond = ["1=1"]
>>cond.AddCond("category_id =",
@params[''category_id''))
>>cond.AddCond("member_id =",
@params[''member_id''))
>>...
>>cond.AddLike("member_name", "%",
@params[''member_name''], "%")
>>
>>... :conditions => cond.join(" and ") ...
>>
>>
>>-- 
>>Posted via http://www.ruby-forum.com/.
>>_______________________________________________
>>Rails mailing list
>>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>http://lists.rubyonrails.org/mailman/listinfo/rails
>>
> 
> 
> 
> 
> 	
> 		
> __________________________________ 
> Yahoo! Mail - PC Magazine Editors'' Choice 2005 
> http://mail.yahoo.com

CSN

2005-Nov-22 11:21 UTC

head link

Re: Re: Building a conditions clause (for find) of multiple opti

--- Peter Bohm <Bohm-Uls/rNv2J19iPCr66r4cLw@public.gmane.org> wrote:
> Just initialize conditions[0] with some always true statements something
> like...
> conditions[0] = " true " or conditions[0] = "1 = 1"
> 
> It''s also possible to use named parameters, which may be a bit
more
> readable - you will have one String with sql and one Hash with named
> parameters
I''m not sure I understand what it''d look like - could you give
a code example?

thanks
csn

> 
> Peter
> 
> CSN wrote:
> > I came up with:
> > 
> > conditions=["var1=1"]
> > if(@params[''member_id''])
> >   conditions[0] += " and member_id=?"
> >   conditions.push(@params[''member_id''])
> > end
> > if(@params[''category_id''])
> >   conditions[0] += " and category_id=?"
> >   conditions.push(@params[''category_id''])
> > end
> > 
> > @items=Items.find(:all, :conditions=>conditions)
> > 
> > It obviously needs a better way of handling the first array element
and/or figuring out
> whether a
> > condition is the first and whether to add " and ".
> > 
> > csn
> > 
> > 
> > --- Christer Nilsson
<janchrister.nilsson-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > 
> > 
> >>I have looked for a solution, but without success. Your solution 
> >>explodes as 2^5=32 possibilities.
> >>
> >>This is my suggestion. I need some advice regarding quotes and sql 
> >>injection.
> >>
> >>class Array
> >>  def AddCond(query, value)
> >>   return if value.blank?
> >>   self << "#{query} #{Report.quote(value)}"
> >>  end
> >>  def AddLike(query, prefix, value, suffix)
> >>   return if value.blank?
> >>   self << "#{query} LIKE
#{Report.quote(prefix+value+suffix)}"
> >>  end
> >>end
> >>
> >>cond = ["1=1"]
> >>cond.AddCond("category_id =",
@params[''category_id''))
> >>cond.AddCond("member_id =",
@params[''member_id''))
> >>...
> >>cond.AddLike("member_name", "%",
@params[''member_name''], "%")
> >>
> >>... :conditions => cond.join(" and ") ...
> >>
> >>
> >>-- 
> >>Posted via http://www.ruby-forum.com/.
> >>_______________________________________________
> >>Rails mailing list
> >>Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> >>http://lists.rubyonrails.org/mailman/listinfo/rails
> >>
> > 
> > 
> > 
> > 
> > 	
> > 		
> > __________________________________ 
> > Yahoo! Mail - PC Magazine Editors'' Choice 2005 
> > http://mail.yahoo.com
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 


	
		
__________________________________ 
Yahoo! Mail - PC Magazine Editors'' Choice 2005 
http://mail.yahoo.com

Lugovoi Nikolai

2005-Nov-22 12:28 UTC

head link

Re: Building a conditions clause (for find) of multiple opti

Well, here''s my suggestion:

class ArFilter

   def initialize(default_cond="1=0", operation =
''AND'')
     @conds = []
     @values = []
     @default_cond = default_cond
     @operation = operation
   end
   def add(cond, *values)
     @conds << cond
     @values += values
   end
   def conditions
     @conds.empty? ? @default_cond : [@conds.join("
"+@operation+" ")] + @values
   end
end


in controller:

def sets
   filter = ArFilter.new("1=1", "OR")
   filter.add("bs_title like ''%'' || ? ||
''%''", params[:qt]) unless params[:qt].blank?
   filter.add("bs_year = ?", params[:qy]) unless  params[:qy].blank?

   @programs = BookSet.find(:all, :order => ''bs_year,
bs_title'', :conditions => filter.conditions)
end

Peter Bohm

2005-Nov-22 12:52 UTC

head link

Re: Building a conditions clause (for find) of multiple opti

I usually use a separate "criteria" class to hold all the search
conditions

class ContactFilter

    @name 

    @phone

    attr_writer :name, :phone

    attr_reader :name, :phone

end

the filter method would look something like this

    def Contact.filter(filter)

        sql = "1 = 1 "

        conditions = Hash.new

        joins = ""

        if filter.name != nil

            sql += " and name = :name"

            conditions[:name] = filter.name

        end

        if filter.phone != nil

            sql += " and (addresses.phone like :phone or
addresses.phone2 like :phone or addresses.fax like :phone)"

            conditions[:phone] = "%#{filter.phone}%"

            joins += " left join addresses on contacts.id
addresses.contact_id "

        end

        return Contact.find(:all, 

                                                        :conditions
=&gt; [sql, conditions],

                                                        :joins =&gt;
joins,

                                                        :order =&gt;
"name") 

    end

off course the hash can be appended different way...

Peter

CSN wrote:

--- Peter Bohm  wrote:

Just initialize conditions[0] with some always true statements something
like...
conditions[0] = " true " or conditions[0] = "1 = 1"

It''s also possible to use named parameters, which may be a bit more
readable - you will have one String with sql and one Hash with named
parameters

I''m not sure I understand what it''d look like - could you give
a code example?

thanks
csn

Peter

CSN wrote:

I came up with:

conditions=["var1=1"]
if(@params[''member_id''])
  conditions[0] += " and member_id=?"
  conditions.push(@params[''member_id''])
end
if(@params[''category_id''])
  conditions[0] += " and category_id=?"
  conditions.push(@params[''category_id''])
end

@items=Items.find(:all, :conditions=&gt;conditions)

It obviously needs a better way of handling the first array element and/or
figuring out

whether a

condition is the first and whether to add " and ".

csn

--- Christer Nilsson  wrote:

I have looked for a solution, but without success. Your solution 
explodes as 2^5=32 possibilities.

This is my suggestion. I need some advice regarding quotes and sql 
injection.

class Array
 def AddCond(query, value)
  return if value.blank?
  self &lt;&lt; "#{query} #{Report.quote(value)}"
 end
 def AddLike(query, prefix, value, suffix)
  return if value.blank?
  self &lt;&lt; "#{query} LIKE
#{Report.quote(prefix+value+suffix)}"
 end
end

cond = ["1=1"]
cond.AddCond("category_id =",
@params[''category_id''))
cond.AddCond("member_id =", @params[''member_id''))
...
cond.AddLike("member_name", "%",
@params[''member_name''], "%")

... :conditions =&gt; cond.join(" and ") ...

-- 
Posted via http://www.ruby-forum.com/.
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

__________________________________ 
Yahoo! Mail - PC Magazine Editors'' Choice 2005 
http://mail.yahoo.com

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

__________________________________ 
Yahoo! Mail - PC Magazine Editors'' Choice 2005 
http://mail.yahoo.com


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

Francois Beausoleil

2005-Nov-22 13:52 UTC

head link

Re: Building a conditions clause (for find) of multiple optional params?

Hi !

2005/11/21, CSN <cool_screen_name90001@yahoo.com>:> I want to be able to find items according to various params - category_id,
member_id, type_id,
> rating, etc. What I have now is something like:
Take a look at "Building the SQL WHERE clause dynamically in Rails"
over at
http://blog.teksol.info/articles/2005/10/31/building-the-sql-where-clause-dynamically-in-rails

Hope that helps !
--
François Beausoleil
http://blog.teksol.info/

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

Leon Leslie

2005-Nov-22 14:06 UTC

head link

Re: Building a conditions clause (for find) of multiple optional params?

Also have a look at the Query by example mixin by Duane Johnson on the list.


On 11/22/05, Francois Beausoleil
<francois.beausoleil-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:>
> Hi !
>
> 2005/11/21, CSN
<cool_screen_name90001-/E1597aS9LQAvxtiuMwx3w@public.gmane.org>:
> > I want to be able to find items according to various params -
> category_id, member_id, type_id,
> > rating, etc. What I have now is something like:
>
> Take a look at "Building the SQL WHERE clause dynamically in
Rails"
> over at
>
http://blog.teksol.info/articles/2005/10/31/building-the-sql-where-clause-dynamically-in-rails
>
> Hope that helps !
> --
> François Beausoleil
> http://blog.teksol.info/
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>

--
First they laugh at you, then they ignore you, then they fight you.
Then you win.
-- Mahatma Karamchand Gandhi


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

Christer Nilsson

2005-Nov-22 15:07 UTC

head link

Re: Building a conditions clause (for find) of multiple opti

Francois!

That really looks nice!
Never mind about 1=1. That will be executed in less than a microsec 
anyway!

If ActiveRecord realized :cond was empty it should suppress "WHERE".

Christer

-- 
Posted via http://www.ruby-forum.com/.

Christer Nilsson

2005-Nov-22 15:14 UTC

head link

Re: Building a conditions clause (for find) of multiple opti

Leon!

Please give a link to Query by example mixin by Duane Johnson

Christer

-- 
Posted via http://www.ruby-forum.com/.

Ezra Zygmuntowicz

2005-Nov-23 06:01 UTC

head link

Re: Re: Building a conditions clause (for find) of multiple opti

Here is another take on building the where clause and builds the  
necessary format including the ? placeholders:

class Where
   def initialize(p,m,u)
     @project = p
     @month = m
     @user = u
   end

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

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

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

p a.build_where_clause
p b.build_where_clause
p c.build_where_clause
p d.build_where_clause

# results
["project = ? and month = ? and user = ?", 1, 2, 3]
["month = ? and user = ?", 1, 2]
["project = ? and user = ?", 1, 2]
["user = ?", 1]



Cheers-

-Ezra Zygmuntowicz
WebMaster
Yakima Herald-Republic Newspaper
ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org
509-577-7732

Apparently Analagous Threads

Search for more reasonably related threads

Rails - Nov 2005 - Building a conditions clause (for find) of multiple optional params?

Building a conditions clause (for find) of multiple optional params?

Re: Building a conditions clause (for find) of multiple opti

Re: Re: Building a conditions clause (for find) of multiple opti

Re: Building a conditions clause (for find) of multiple opti

Re: Re: Building a conditions clause (for find) of multiple opti

Re: Building a conditions clause (for find) of multiple opti

Re: Building a conditions clause (for find) of multiple opti

Re: Building a conditions clause (for find) of multiple optional params?

Re: Building a conditions clause (for find) of multiple optional params?

Re: Building a conditions clause (for find) of multiple opti

Re: Building a conditions clause (for find) of multiple opti

Re: Re: Building a conditions clause (for find) of multiple opti

Apparently Analagous Threads