Building a conditions clause (for find) of multiple optional

Rails
1

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

2

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 ") …

3

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. [email protected] wrote:

end
cond.AddLike(“member_name”, “%”, @params[‘member_name’], “%”)

Yahoo! Mail - PC Magazine Editors’ Choice 2005

4

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

5

— Peter B. [email protected] 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
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

Yahoo! Mail - PC Magazine Editors’ Choice 2005

6

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

7

Hi !

2005/11/21, CSN [email protected]:

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 !

8

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

9

Leon!

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

Christer

10

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

11

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
[email protected]
509-577-7732

12

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails