Forum: Ruby on Rails Dynamic Where clause for :conditions in AR::Base.find

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.
8e44c65ac5b896da534ef2440121c953?d=identicon&s=25 ezra (Guest)
on 2005-12-01 18:35
(Received via mailing list)
I have come up with a nice little solution for building the Where
clause dynamically for the : conditions part of a find method in your
models. You can put this wherever you like and require it or you can
put it inside your model class that will use it.

Here it is:

class Where
   # Utility class to dynamically create the where clause
   # for a rails find method. Initialize expects a nested
   # array like:  [[:project, 3], [:month, 7], [:user, 0]]
   # where each nested array contains a symbol of the column
   # name and a value to query for. Lets say that you have
   # this hash in your +params+ :
   # params = { :person => {:name => "dave" :city => "Yakima"}}
   # You can use this class to build the where clause like so:
   # where = Where.new([ [:user, params[:person][:name]], [:city,
params[:person][:city]] ])
   # MyModel.find(:all, :conditions => where.build_where_clause)
   def initialize(options=[])
     @opts = options
     @opts.each do |pair|
       instance_variable_set("@#{pair[0].to_sym}", pair[1])
     end
   end

   def build_where_clause
     q = []
     ary = []
     @opts.each do |pair|
       iv = instance_variable_get("@#{pair[0]}")
       unless iv.nil? || iv.to_s == ''
	      q << "#{pair[0]} = ?"
	      ary << iv
	    end
     end
     return [q.join(" and ")].concat(ary)
   end

end # End Where

# Some examples:

a = Where.new([[:project, 3], [:month, 7], [:user, 0]])
p a.build_where_clause
# => ["project = ? and month = ? and user = ?", 3, 7, 0]

b = Where.new([[:post, 6], [:user, "admin"], [:comment, 12]])
p b.build_where_clause
# => ["post = ? and user = ? and comment = ?", 6, "admin", 12]

# built from params. You can see that the params[:person][:state] is
empty.
# this class will take that into consideration and leave that part
out of the where clause
params = {:id => 17, :person => {:city => "Yakima", :name =>
"dave", :state => ""}}
c = Where.new([[:user, params[:person][:name]], [:city, params
[:person][:city]], [:state, params[:person][:state]]])
p c.build_where_clause
# => ["user = ? and city = ?", "dave", "Yakima"]


Hope its useful to someone out there!

Cheers-

-Ezra Zygmuntowicz
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
ezra@yakima-herald.com
Af95bdaf87958c40150b813e94381bfd?d=identicon&s=25 Christer Nilsson (christer)
on 2005-12-01 18:53
Nice job, Ezra!

Please also show how the following clauses are implemented:

name LIKE 'chr%'
number LIKE '%123'
recipe LIKE '%butter%'
date <= '2005-12-01'

Christer
8e44c65ac5b896da534ef2440121c953?d=identicon&s=25 ezra (Guest)
on 2005-12-01 19:56
(Received via mailing list)
On Dec 1, 2005, at 9:53 AM, Christer Nilsson wrote:

> Nice job, Ezra!
>
> Please also show how the following clauses are implemented:
>
> name LIKE 'chr%'
> number LIKE '%123'
> recipe LIKE '%butter%'
> date <= '2005-12-01'
>
> Christer


Hrmm.. Good idea! I will flesh out this class to be more flexible and
support more variations on the where clause part. How about posting
me a list of all the different things you would like to see supported
the most?

Thanks-

-Ezra Zygmuntowicz
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
ezra@yakima-herald.com
Af95bdaf87958c40150b813e94381bfd?d=identicon&s=25 Christer Nilsson (christer)
on 2005-12-01 20:34
This is a copy of a more elaborate comment:

In the same WHERE clause it should be possible to mix the operators:

WHERE name LIKE 'chr%'
AND city LIKE '%FURT'
AND skills LIKE '%plumbing%'
AND date <= '2006-12-01'
AND age >= 25
AND age <= 50
AND sex = 'female'

And remember, all 2^7 = 128 combinations should be possible!

If age and sex are uninteresting, collapse to:

WHERE name LIKE 'chr%'
AND city LIKE '%FURT'
AND skills LIKE '%plumbing%'
AND date <= '2006-12-01'

How can this be specified in a readable form?

My long term goal is to NEVER see SQL. This is one of the problems on
the journey.

Christer
F3dc06f587d1ff4c7366b102bfda9204?d=identicon&s=25 monch1962 (Guest)
on 2005-12-01 20:45
(Received via mailing list)
How about:
value IN (select ...)

I'm finding myself using that more and more often lately...

Regards

Dave M.
0bcc972918891ef358b0b3dfb4e7f18b?d=identicon&s=25 hammed (Guest)
on 2005-12-02 01:33
(Received via mailing list)
since you asked... please consider adding

BETWEEN date1 AND date2

A good search filter plugin would be extremely useful.
5085ba37ba624a788e04a842cd09bd6e?d=identicon&s=25 Jens-Christian Fischer (Guest)
on 2005-12-13 15:22
(Received via mailing list)
In respone to all the wishes for an extension of Ezra's original
class, I created the "where" plugin

Here's the description as it is on the Wiki:

An easier way to create where clauses.

Creates a class for easily building the where clauses for the
Model.find(..., :conditions => condition )

where = InVisible::Where.new( [[ :first, "Jens-Christian"], [ :last,
"Fischer" ] ] )
where.add( [ [ :age, :gt, 30 ] ] )
where.add( [ [ :city, :like, "Zur%" ] ] )

Adress.find( :all, :condition => where.build_where_clause )


This is based on the Where class, that Ezra Zygmuntowic sent to the
rails mailing list on 2005-12-01 and includes all the wishes that
came. The following is understood:
[ :age, 34 ] => â??age = ?â?
[ :age, :eq, 34 ] => â??age = ?â?
[ :age, :gt, 34 ] => â??age > ?â?
[ :age, :lt, 34 ] => â??age < ?â?
[ :age, :gteq, 34 ] => â??age => ?â?
[ :age, :lteq, 34 ] => â??age <= ?â?
[ :name, :like, â??abc%â? ] => â??name LIKE ?â?
[ :date, :between, date1, date2 ] => â??date BETWEEN ? and ?â?
[ :ip, [:gt, :inet], â??12.34.56.78/24â? ] => â??ip > inet ?â? (letâ??s you
type the value (for example the inet type in Postgres)
[ :sql, â??hosts.id = logs.host_id and hosts.nameâ?, :like,
â??123.23.45.67â?] => â??hosts.id = logs.host_id and hosts.name LIKE ?
(for those time when you really need SQL. Use any symbol that starts
with� (so you can have multiple SQL additions)
There also is an extension to ActiveRecord::Base that allows you to
get the SQL query that AR will create. Use like this:

Model.get_sql( :all, :conditions =>..., :limit => ..., ) with the
same parameters as the Model.find() method. Does not honor
the :include option

svn: http://invisible.ch/svn/projects/plugins/where

Have fun playing with it

Jens-Christian
4bd34a2216dc8bdbf1f017f64e4d59e8?d=identicon&s=25 Kyle Maxwell (Guest)
on 2005-12-13 18:36
(Received via mailing list)
Why is this better than SQL?
5085ba37ba624a788e04a842cd09bd6e?d=identicon&s=25 Jens-Christian Fischer (Guest)
on 2005-12-13 20:40
(Received via mailing list)
Am 13.12.2005 um 18:36 schrieb Kyle Maxwell:

> Why is this better than SQL?

I have a controller with a form that filters are rather large data-
set. Different parameters need different treatment (like the "inet"
datatype). Building the SQL string manually is certainly doable, but
a bit of a mess. Using the "Where" Class, make imo for an easier to
read controller method.

And for easy forms you can more or less just use the params hash (as
Ezra showed in his original example)

cu jc
00b25ade726ce4fb538d3d8cf47a750b?d=identicon&s=25 Harvey Bernstein (Guest)
on 2005-12-28 17:26
(Received via mailing list)
Hello

This plugin seems to be just what I've been looking for.  I'm currently
working on my first real rails project and the problem I'm having is
bound
to be very trivial.  I would like to have a search form that contains
lots
of options like:-

Name of Provider
Min Price
Max Price
Type of Accommodation
Location (Zip Code / Postcode)

The user should be able to optionally select any of the various
criteria.
If they entered the name of the provider the query would use a %like%
condition.  Also, if the user entered both the min and the max price the
between condition would be used. Whereas if the user entered just the
max
price the less than SQL operator would be used.

I'm not entirely sure how to actually build the Where clause.  Is it
possible to pass in the params hash or do you have to build the where
clause
in the controller?  I'm just having problems working out what code would
be
in the form and what would be in the controller.  Any sample code would
be
greatly appreciated!

Thanks in advance.
Regards
Harvey


On 13/12/05 19:36, "Jens-Christian Fischer" <jcf@invisible.ch> wrote:

> And for easy forms you can more or less just use the params hash (as
> Ezra showed in his original example)


Thanks in advance
Harvey


This e-mail has been scanned for all viruses by MessageLabs.
821395fe70906c8290df7f18ac4ac6cf?d=identicon&s=25 Rick Olson (Guest)
on 2005-12-28 17:35
(Received via mailing list)
> Creates a class for easily building the where clauses for the
> Model.find(..., :conditions => condition )
>
> where = InVisible::Where.new( [[ :first, "Jens-Christian"], [ :last,
> "Fischer" ] ] )
> where.add( [ [ :age, :gt, 30 ] ] )
> where.add( [ [ :city, :like, "Zur%" ] ] )
>
> Adress.find( :all, :condition => where.build_where_clause )

I'm not crazy about that API at all.  Why not hide all that from me
and let me do:

Model.find_with_conditions(:all, :order => ....) do |where|
  where << [:age, :gt, 30]
  where << [:city, :like, "Zur%"]
end

That way the instantiation of the Where class is invisible to the
coder and could even be swapped out with a different class if desired.

--
rick
http://techno-weenie.net
Df040ca3576504b24a73744179903277?d=identicon&s=25 Tobias Luetke (Guest)
on 2005-12-28 23:06
(Received via mailing list)
If you like to write it like this you can still make conditions out of
it in one single line of ruby:

c = [ [ :age, '=', 30 ],
        [ :city, 'LIKE', "Zur%" ] ]

conditions = c.collect { |k,r,v| "#{k} #{r} #{v}"}.join(' AND ')

On 12/13/05, Jens-Christian Fischer <jcf@invisible.ch> wrote:
> where = InVisible::Where.new( [[ :first, "Jens-Christian"], [ :last,
> [ :age, 34 ] => "age = ?"
> "123.23.45.67"] => "hosts.id = logs.host_id and hosts.name LIKE ?
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>
>


--
Tobi
http://jadedpixel.com    - modern e-commerce software
http://typo.leetsoft.com - Open source weblog engine
http://blog.leetsoft.com - Technical weblog
5085ba37ba624a788e04a842cd09bd6e?d=identicon&s=25 Jens-Christian Fischer (Guest)
on 2006-01-01 02:03
(Received via mailing list)
> I'm not crazy about that API at all.  Why not hide all that from me
> and let me do:
>
> Model.find_with_conditions(:all, :order => ....) do |where|
>   where << [:age, :gt, 30]
>   where << [:city, :like, "Zur%"]
> end

I just updated the plugin to allow for this case. ( And I dropped the
requirement for the :gt, :lt etc symbols (just use '<', '>', etc.) )

happy new year
jc
2073c7e0e7d2311727b680a887a375be?d=identicon&s=25 Leon Leslie (Guest)
on 2006-01-01 13:16
(Received via mailing list)
Happy new Year Jens-Christian,

I am considering this a new years gift.... That was quick...

Thanks Rick for the idea....

l
2073c7e0e7d2311727b680a887a375be?d=identicon&s=25 Leon Leslie (Guest)
on 2006-01-01 13:43
(Received via mailing list)
When i use pagination it is still ugly.

> where = InVisible::Where.new( [[ :first, "Jens-Christian"], [ :last,
> "Fischer" ] ] )
> where.add( [ [ :age, :gt, 30 ] ] )
> where.add( [ [ :city, :like, "Zur%" ] ] )

@address_pages, @addresses = paginate :address,
       :conditions => where.build_where_clause, :per_page => 10,
:order_by
=> 'first'

How can I hide and clean up my code when using pagination.

l
132a94ca65959bda6c74fae54bff2425?d=identicon&s=25 Ezra Zygmuntowicz (Guest)
on 2006-01-01 23:08
(Received via mailing list)
When I was originally playing with this I came up with this little
class : http://rubyurl.com/DxL

With that one you can build the conditions with a lightly nicer syntax:

c = Cond.new do
   month '<=', 11
   year '=', 2005
   name 'LIKE', 'ruby%'
end

c.where
# =>  ["month <= ? and year = ? and name LIKE ?", 11, 2005, "ruby%"]

@address_pages, @addresses = paginate :address,
        :conditions => c.where, :per_page => 10, :order_by => 'first'


	It still is a little verbose compared to just writing it out by hand
but it does abstract things a little bit.


Cheers-
-Ezra


On Jan 1, 2006, at 4:41 AM, Leon Leslie wrote:

>
> Thanks Rick for the idea....
> >   where << [:city, :like, "Zur%"]
> InVisible GmbH, Langgrütstrasse 172, 8047 Zürich
>
>
> --
> First they laugh at you, then they ignore you, then they fight you.
> Then you win.
> -- Mahatma Karamchand Gandhi
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

-Ezra Zygmuntowicz
WebMaster
Yakima Herald-Republic Newspaper
ezra@yakima-herald.com
509-577-7732
5085ba37ba624a788e04a842cd09bd6e?d=identicon&s=25 Jens-Christian Fischer (Guest)
on 2006-01-01 23:56
(Received via mailing list)
> end
Ahh - I didn't realize you already had that class fleshed out. I'm
fairly new to methods taking blocks, so I didn't know how to use your
syntax (which I like) with the

Model.find_with_conditions ....

method. The "method_missing" won't work, because it would override
ActiveRecords method_missing, right?

How to integrate those two concepts?

jc
8e44c65ac5b896da534ef2440121c953?d=identicon&s=25 Ezra Zygmuntowicz (Guest)
on 2006-01-02 01:30
(Received via mailing list)
On Jan 1, 2006, at 2:56 PM, Jens-Christian Fischer wrote:

>> end
> How to integrate those two concepts?
> http://lists.rubyonrails.org/mailman/listinfo/rails
I will have to take a look at how you implemented the
find_with_conditions and get back to you. It should be possible
though. There might be something that could be done with calling
super in the method_missing to make sure we don't entirely override
AR's method_missing. Or maybe the best way would be to keep the
method_missing confined in the Cond class and yield a new Cond object
when find_with_conditions gets called.

I will take a peek and get back to you with a patch.

Cheers-
-Ezra Zygmuntowicz
WebMaster
Yakima Herald-Republic Newspaper
ezra@yakima-herald.com
509-577-7732
132a94ca65959bda6c74fae54bff2425?d=identicon&s=25 Ezra Zygmuntowicz (Guest)
on 2006-01-02 01:34
Jens-Christian-

	So I played with things a bit to make the Cond class take a block and
make the find_with_conditions pass the block into the Cond.new
statement. Here is the simplified code that if you made some changes to
your Where class you could use the method_missing interface. Its using
my Cond class instead of your Where class for now and a simplified
version of the find_with_conditions just for examples sake:

class Cond

  def initialize(&block)
    @args = []
    instance_eval(&block) if block_given?
  end

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

  def where(args=@args)
    args.each do |triplet|
      instance_variable_set("@#{triplet[0].to_sym}", triplet[2])
    end
    q = []
    ary = []
    args.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

class Base
  def self.find_with_conditions(*args, &block)
    cond = Cond.new(&block)
   #code goes here for dealing with the options hash merge and calling
find.
   # simplified for examples sake we will just return the where clause
for now
    cond.where
  end
end

a = Base.find_with_conditions do
  month '<=', 11
  year '=' , "red"
end

p a
#=> ["month <= ? and year = ?", 11, "red"]

	So if you make your Where class use an initialize method that takes a
block like my Cond class above does, then you can use the nice syntax
with either find_with_conditions by passing the block into the
constructor like this:  Cond.new(&block) or when you just want to build
the where clause by itself like this:

c = Cond.new do
  month '<=', 11
  year '=', 2005
  name 'LIKE', 'ruby%'
end

c.where
# =>  ["month <= ? and year = ? and name LIKE ?", 11, 2005, "ruby%"]



	Play with that for a bit and see if you like it. I think it will be the
easiest way to get the syntax we like. I will make this a patch for your
where plugin later today unles you would rather do it? Let me know.

Cheers-

-Ezra Zygmuntowicz
WebMaster
Yakima Herald-Republic Newspaper
ezra@yakima-herald.com
509-577-7732
8e44c65ac5b896da534ef2440121c953?d=identicon&s=25 Ezra Zygmuntowicz (Guest)
on 2006-01-02 02:28
(Received via mailing list)
Jens-Christian-

	So I played with things a bit to make the Cond class take a block
and make the find_with_conditions pass the block into the Cond.new
statement. Here is the simplified code that if you made some changes
to your Where class you could use the method_missing interface. Its
using my Cond class instead of your Where class for now and a
simplified version of the find_with_conditions just for examples sake:

class Cond

   def initialize(&block)
     @args = []
     instance_eval(&block) if block_given?
   end

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

   def where(args=@args)
     args.each do |triplet|
       instance_variable_set("@#{triplet[0].to_sym}", triplet[2])
     end
     q = []
     ary = []
     args.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

class Base
   def self.find_with_conditions(*args, &block)
     cond = Cond.new(&block)
    #code goes here for dealing with the options hash merge and
calling find.
    # simplified for examples sake we will just return the where
clause for now
     cond.where
   end
end

a = Base.find_with_conditions do
   month '<=', 11
   year '=' , "red"
end

p a
#=> ["month <= ? and year = ?", 11, "red"]

	So if you make your Where class use an initialize method that takes
a block like my Cond class above does, then you can use the nice
syntax with either find_with_conditions by passing the block into the
constructor like this:  Cond.new(&block) or when you just want to
build the where clause by itself like this:

c = Cond.new do
   month '<=', 11
   year '=', 2005
   name 'LIKE', 'ruby%'
end

c.where
# =>  ["month <= ? and year = ? and name LIKE ?", 11, 2005, "ruby%"]



	Play with that for a bit and see if you like it. I think it will be
the easiest way to get the syntax we like. I will make this a patch
for your where plugin later today unles you would rather do it? Let
me know.

Cheers-

-Ezra Zygmuntowicz
WebMaster
Yakima Herald-Republic Newspaper
ezra@yakima-herald.com
509-577-7732
5085ba37ba624a788e04a842cd09bd6e?d=identicon&s=25 Jens-Christian Fischer (Guest)
on 2006-01-02 12:00
(Received via mailing list)
>
> 	Play with that for a bit and see if you like it. I think it will
> be the
> easiest way to get the syntax we like. I will make this a patch for
> your
> where plugin later today unles you would rather do it? Let me know.

Looks good (from skimming over the code). I'm returning from our
mountain retreat to the city today, so I likely won't have time to
write that patch. Feel free to do it, I'll integrate it later then.

thanks
jc
5085ba37ba624a788e04a842cd09bd6e?d=identicon&s=25 Jens-Christian Fischer (Guest)
on 2006-01-02 22:01
(Received via mailing list)
>> where plugin later today unles you would rather do it? Let me know.
>
> Looks good (from skimming over the code). I'm returning from our
> mountain retreat to the city today, so I likely won't have time to
> write that patch. Feel free to do it, I'll integrate it later then.

I have update the plugin to use your class (removing my Where class
in the process, thus breaking old code) and created a descriptive
page for the plugin:

http://blog.invisible.ch/?p=497

cu jc
8e44c65ac5b896da534ef2440121c953?d=identicon&s=25 Ezra Zygmuntowicz (Guest)
on 2006-01-03 01:50
(Received via mailing list)
On Jan 2, 2006, at 1:00 PM, Jens-Christian Fischer wrote:

> http://blog.invisible.ch/?p=497
>
> cu jc


JC-

	Cool, looks good! I like that you can still use the sql and  the
between operator as well. I think this will be a pretty useful
plugin. Nice job.

Cheers-
-Ezra Zygmuntowicz
WebMaster
Yakima Herald-Republic Newspaper
ezra@yakima-herald.com
509-577-7732
5085ba37ba624a788e04a842cd09bd6e?d=identicon&s=25 Jens-Christian Fischer (Guest)
on 2006-01-03 10:33
(Received via mailing list)
I just found a problem that I'm not sure how to solve:

Converting this:

Task.find(:all, :conditions => ['project_id = ?', self.id] )

to

Task.find_with_conditions(:all) do
   project_id '=', self.id
end

breaks horrible, because "self.id" in the block references god knows
what (probably the Cond block) instead of the ActiveRecord object. A
work around is to use a temporary variable:

id = self.id
Task.find_with_conditions(:all) do
   project_id '=', id
end

Hmm - not that nice. Any ideas on how to solve that?

jc
132a94ca65959bda6c74fae54bff2425?d=identicon&s=25 Ezra Zygmuntowicz (Guest)
on 2006-01-03 19:16
(Received via mailing list)
On Jan 3, 2006, at 1:31 AM, Jens-Christian Fischer wrote:

> end
> Hmm - not that nice. Any ideas on how to solve that?
>
> jc


Jens-

	I think that I know a way around it. If you wrap it like "#
{self.id}"  , I think it will get evaluated before self refers to the
Cond class inside the block.	Can you try it and confirm that this works?


> Task.find_with_conditions(:all) do
>   project_id '=', "#{self.id}"
> end

Thanks-

-Ezra Zygmuntowicz
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
ezra@yakima-herald.com
This topic is locked and can not be replied to.