Forum: Ruby on Rails Building a conditions clause (for find) of multiple optional

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
cool_screen_name90001 (Guest)
on 2005-11-22 05:01
(Received via mailing list)
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 N. (Guest)
on 2005-11-22 08:58
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 ") ...
cool_screen_name90001 (Guest)
on 2005-11-22 10:59
(Received via mailing list)
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 N. <removed_email_address@domain.invalid> wrote:

>   end
> cond.AddLike("member_name", "%", @params['member_name'], "%")
>
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
Bohm (Guest)
on 2005-11-22 12:44
(Received via mailing list)
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
cool_screen_name90001 (Guest)
on 2005-11-22 13:24
(Received via mailing list)
--- Peter B. <removed_email_address@domain.invalid> 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


> > end
> >
> >>injection.
> >>end
> >>--
> >
> >
> > __________________________________
> > Yahoo! Mail - PC Magazine Editors' Choice 2005
> > http://mail.yahoo.com
>
> _______________________________________________
> Rails mailing list
> removed_email_address@domain.invalid
> http://lists.rubyonrails.org/mailman/listinfo/rails
>





__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
meadow.nnick (Guest)
on 2005-11-22 14:32
(Received via mailing list)
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
Bohm (Guest)
on 2005-11-22 14:56
(Received via mailing list)
_______________________________________________
Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails
francois.beausoleil (Guest)
on 2005-11-22 15:53
(Received via mailing list)
Hi !

2005/11/21, CSN <removed_email_address@domain.invalid>:
> 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/buildi...

Hope that helps !
leonleslie (Guest)
on 2005-11-22 16:09
(Received via mailing list)
Also have a look at the Query by example mixin by Duane J. on the
list.
Christer N. (Guest)
on 2005-11-22 17:07
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
Christer N. (Guest)
on 2005-11-22 17:14
Leon!

Please give a link to Query by example mixin by Duane J.

Christer
ezra (Guest)
on 2005-11-23 08:03
(Received via mailing list)
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 Z.
WebMaster
Yakima Herald-Republic Newspaper
removed_email_address@domain.invalid
509-577-7732
This topic is locked and can not be replied to.