Dynamic where clause - revisited


#1

In many apps there is a form filtering out rows from a recordset.
The user can fill in zero or more of these fields.
Example: Buying a flat.
Living Area, Number of rooms, Price, Date, City etc.
Often these parameters have minimum and maximum.

I have made some helping functions to make this process smother:

def q(value) # quote
value.kind_of?(String) ? “’#{value}’” : “#{value}”
end
def a(*arg) # and
arg.compact.join(" and “)
end
def o(*arg) # or
arg.nil? ? nil : “(#{arg.compact.join(” or “)})”
end
def eq(name, value)
value.nil? || value==”" ? nil : “#{name}=#{q(value)}”
end
def neq(name, value)
value.nil? || value=="" ? nil : “#{name}!=#{q(value)}”
end
def lt(name,value)
value.nil? || value=="" ? nil : “#{name}<#{q(value)}”
end
def lte(name,value)
value.nil? || value=="" ? nil : “#{name}<=#{q(value)}”
end
def gt(name,value)
value.nil? || value=="" ? nil : “#{name}>#{q(value)}”
end
def gte(name,value)
value.nil? || value=="" ? nil : “#{name}>=#{q(value)}”
end
def between(name,low,high)
a(gte(name,low),lt(name,high))
end
def i(name,list) # in
list.empty? ? nil : “#{name} in (#{list.map! {|e| q(e)}.join(”,")})"
end
def like(name,value)
[nil, “”, “%”, “%%”].include?(value) ? nil : “#{name} like
#{q(value)}”
end

def assert(expect,actual)
expect==actual ? print(".") : print("\nexpect: #{expect}\nactual:
#{actual}\n")
end
def test
assert “1”, q(1)
assert “‘a’”, q(“a”)
assert “’’”, q("")
assert “price>=500 and price<1000”, between(“price”,500,1000)
assert “price>100 and price<500”, a(gt(“price”,100),
lt(“price”,500))
assert “rooms in (1,2,3)”, i(“rooms”,[1,2,3])
assert “friends in (‘adam’,‘eve’)”, i(“friends”,[‘adam’,‘eve’])
assert nil, i(“rooms”,[])

assert "size>100", gt("size",100)
assert "size>=100", gte("size",100)
assert "size<500", lt("size",500)
assert "size<=500", lte("size",500)
assert "price<500", a(gt("price",nil), lt("price",500))
assert "", a(gt("price",nil), lt("price",nil))

assert "sex='female'", eq("sex", "female")
assert "sex!='male'", neq("sex", "male")

size = a(gt("size",100), lt("size",150))
price = a(gt("price",nil), lt("price",500))
city = eq("city", "Berlin")
assert "size>100 and size<150 and (price<500 or 

city=‘Berlin’)",a(size, o(price,city))
assert “(size>100 and size<150 or price<500 and
city=‘Berlin’)”,o(size, a(price,city))

assert "city='Berlin'", eq("city","Berlin")
assert nil, eq("city",nil)

assert "city like '%furt'",like("city","%furt")
assert "city like '%furt%'",like("city","%furt%")
assert "city like 'furt%'",like("city","furt%")
assert nil,like("city","")
assert nil,like("city",nil)
assert nil,like("city","%")
assert nil,like("city","%%")

#assert "0<b and b<c and c<9", ramp("0","b","c","9")
#assert "0<b and b<9", ramp("0","b",nil,"9")
#assert "0<=b and b<=9", rampeq("0","b",nil,"9")

print("\nReady!")

end

test

Application code sample: =======================================

cond = a(like(“r.flightnumber”, “%#{@report.flightnumberFilter}%”),
like(“r.description”, “%#{@report.descriptionFilter}%”),
like(“u.name”, “%#{@report.pilotFilter}%”),
gte(“r.flightdate”, @report.fromdateFilter),
lte(“r.flightdate”, @report.todateFilter))

cond=“1=1” if cond=="" # Empty WHERE clause forbidden

@reports = Report.find(:all, :select => “r.*”, :joins => “AS r INNER
JOIN Users AS u ON r.user_id = u.id”, :conditions => cond, :order =>
“flightnumber, flightdate”)

Christer


#2

Hello Christer !

2005/12/2, Christer N. removed_email_address@domain.invalid:

def test

You’re missing: assert ‘’, q(nil)

and a lot of other ones too.

Interesting stuff !


#3

Francois, do you have an assertion that fails ?


#4

2005/12/2, Christer N. removed_email_address@domain.invalid:

Francois, do you have an assertion that fails ?

No, I mean to say that you don’t have a test case for when nil is
passed to your q method.

Thanks,


#5

On Dec 2, 2005, at 8:54 AM, Francois B. wrote:

2005/12/2, Christer N. removed_email_address@domain.invalid:

Christer and Francois-

Here is my new version that makes the setup and building of the

where clause into less code. It doesn’t handle everything yet but
check it out. I think its a good start on a small dsl. I still need
to convert the Where.new to take a block instead of how it works now
but let me know what you think.

class Where

attr_reader :args

def initialize
@args = []
end

def method_missing(sym, *args)
@args << [sym,args.flatten].flatten
end

def build(options=@args)
@opts = options
@opts.each do |triplet|
instance_variable_set("@#{triplet[0].to_sym}", triplet[2])
end
self.where
end

def where
q = []
ary = []
@opts.each do |triplet|
iv = instance_variable_get("@#{triplet[0]}")
unless iv.nil? || iv.to_s == ‘’
q << “#{triplet[0]} #{triplet[1]} ?”
ary << iv
end
end
return [q.join(" and ")].concat(ary)
end

end

Usage

cond = Cond.new
cond.month ‘<=’, 11
cond.year ‘=’, 2005
cond.name ‘LIKE’, ‘ruby%’
cond.build

=> [“month <= ? and year = ? and name LIKE ?”, 11, 2005, “ruby%”]

cond = Cond.new
cond.city ‘LIKE’, ‘yakima%’
cond.state ‘=’, ‘WA’
cond.comment ‘LIKE’, ‘%ruby%rails%’
cond.date ‘<=’, ‘12-05-2005’
cond.build

=> [“city LIKE ? and state = ? and comment LIKE ? and date <= ?”,

“yakima%”, “WA”, “%ruby%rails%”, “12-05-2005”]

Or:

params = {:person=>{:name => ‘Ezra’, :city => ‘Yakima’, :state => ‘WA’}}

cond = Cond.new
cond.name ‘LIKE’, “%#{params[:person][:name]}%”
cond.city ‘=’, params[:person][:city]
cond.state ‘=’, params[:person][:state]
cond.build

=> [“name LIKE ? and city = ? and state = ?”, “%Ezra%”, “Yakima”,

“WA”]

What do you think? Is it worth pursuing and fleshing out more
functionality?

Cheers-

-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
removed_email_address@domain.invalid


#6

On Dec 2, 2005, at 8:21 AM, Christer N. wrote:

end
value.nil? || value=="" ? nil : “#{name}!=#{q(value)}”
def gte(name,value)
[nil, “”, “%”, “%%”].include?(value) ? nil : “#{name} like
assert “’’”, q(”")
assert “size<=500”, lte(“size”,500)
city=‘Berlin’)",a(size, o(price,city))
assert nil,like(“city”,nil)
test

@reports = Report.find(:all, :select => “r.*”, :joins => “AS r INNER
JOIN Users AS u ON r.user_id = u.id”, :conditions => cond, :order =>
“flightnumber, flightdate”)

Christer

Nice job Christer. Looks good. Now you just need to come up with a
way to escape the values so there is no sql injection attack vectors.
If we both keep it up we will eventually come up with somehting nice.

Cheers-

-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
removed_email_address@domain.invalid


#7

On Dec 2, 2005, at 11:16 AM, Ezra Z. wrote:

check it out. I think its a good start on a small dsl. I still need
to convert the Where.new to take a block instead of how it works
now but let me know what you think.

class Where
class Cond

sorry about that typo on my part.

ary = []

end
cond = Cond.new
params = {:person=>{:name => ‘Ezra’, :city => ‘Yakima’, :state =>
What do you think? Is it worth pursuing and fleshing out more
removed_email_address@domain.invalid


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
removed_email_address@domain.invalid