Forum: Ruby on Rails Dynamic where clause - revisited

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Christer N. (Guest)
on 2005-12-02 18:21
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
francois.beausoleil (Guest)
on 2005-12-02 18:45
(Received via mailing list)
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 !
Christer N. (Guest)
on 2005-12-02 18:50
Francois, do you have an assertion that fails ?
francois.beausoleil (Guest)
on 2005-12-02 18:57
(Received via mailing list)
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,
ezra (Guest)
on 2005-12-02 21:20
(Received via mailing list)
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
ezra (Guest)
on 2005-12-02 21:28
(Received via mailing list)
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
ezra (Guest)
on 2005-12-02 21:56
(Received via mailing list)
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
This topic is locked and can not be replied to.