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
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 ") …
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
[object
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
— 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
[object
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
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 !
Also have a look at the Query by example mixin by Duane J. on the
list.
Leon!
Please give a link to Query by example mixin by Duane J.
Christer
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
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