# Dynamic where clause - revisited

**URL:** <https://www.ruby-forum.com/t/dynamic-where-clause-revisited/49328>\
**Category:** Rails\
**Created:** [December 2, 2005, 5:21pm UTC](https://www.ruby-forum.com/t/dynamic-where-clause-revisited/49328 "2005-12-02T17:21:17Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![christer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/a5b964/32.png) [@christer](https://www.ruby-forum.com/u/christer)\
**Post date:** [December 2, 2005, 5:21pm UTC](https://www.ruby-forum.com/t/dynamic-where-clause-revisited/49328/1 "2005-12-02T17:21:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![christer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/a5b964/32.png) [@christer](https://www.ruby-forum.com/u/christer)\
**Post date:** [December 2, 2005, 5:45pm UTC](https://www.ruby-forum.com/t/dynamic-where-clause-revisited/49328/2 "2005-12-02T17:45:15Z")

</div>

Hello Christer !

2005/12/2, Christer N. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> def test

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

and a lot of other ones too.

Interesting stuff !

---

<div class="post-metadata">

**Author:** ![christer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/a5b964/32.png) [@christer](https://www.ruby-forum.com/u/christer)\
**Post date:** [December 2, 2005, 5:50pm UTC](https://www.ruby-forum.com/t/dynamic-where-clause-revisited/49328/3 "2005-12-02T17:50:17Z")

</div>

Francois, do you have an assertion that fails ?

---

<div class="post-metadata">

**Author:** ![christer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/a5b964/32.png) [@christer](https://www.ruby-forum.com/u/christer)\
**Post date:** [December 2, 2005, 5:57pm UTC](https://www.ruby-forum.com/t/dynamic-where-clause-revisited/49328/4 "2005-12-02T17:57:24Z")

</div>

2005/12/2, Christer N. [removed\_email\_address@domain.invalid](mailto: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,

---

<div class="post-metadata">

**Author:** ![christer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/a5b964/32.png) [@christer](https://www.ruby-forum.com/u/christer)\
**Post date:** [December 2, 2005, 8:20pm UTC](https://www.ruby-forum.com/t/dynamic-where-clause-revisited/49328/5 "2005-12-02T20:20:05Z")

</div>

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

> 2005/12/2, Christer N. [removed\_email\_address@domain.invalid](mailto: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](http://yakimaherald.com)  
509-577-7732  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![christer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/a5b964/32.png) [@christer](https://www.ruby-forum.com/u/christer)\
**Post date:** [December 2, 2005, 8:28pm UTC](https://www.ruby-forum.com/t/dynamic-where-clause-revisited/49328/6 "2005-12-02T20:28:11Z")

</div>

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](http://yakimaherald.com)  
509-577-7732  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![christer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/a5b964/32.png) [@christer](https://www.ruby-forum.com/u/christer)\
**Post date:** [December 2, 2005, 8:56pm UTC](https://www.ruby-forum.com/t/dynamic-where-clause-revisited/49328/7 "2005-12-02T20:56:56Z")

</div>

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](http://lists.rubyonrails.org/mailman/listinfo/rails)

-Ezra Z.  
Yakima Herald-Republic  
WebMaster  
[http://yakimaherald.com](http://yakimaherald.com)  
509-577-7732  
removed\_email\_address@domain.invalid