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