Dynamic Where clause for :conditions in AR::Base.find


#1

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 Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
removed_email_address@domain.invalid


#2

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


#3

On Dec 1, 2005, at 9:53 AM, Christer N. 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 Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
removed_email_address@domain.invalid


#4

How about:
value IN (select …)

I’m finding myself using that more and more often lately…

Regards

Dave M.


#5

since you asked… please consider adding

BETWEEN date1 AND date2

A good search filter plugin would be extremely useful.


#6

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


#7

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


#8

Am 13.12.2005 um 18:36 schrieb Kyle M.:

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


#9

Why is this better than SQL?


#10

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 F.” removed_email_address@domain.invalid 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.


#11

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 F. removed_email_address@domain.invalid 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
removed_email_address@domain.invalid
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


#12

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


#13

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


#14

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


#15

Happy new Year Jens-Christian,

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

Thanks Rick for the idea…

l


#16

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


#17

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 L. 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
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

-Ezra Z.
WebMaster
Yakima Herald-Republic Newspaper
removed_email_address@domain.invalid
509-577-7732


#18

On Jan 1, 2006, at 2:56 PM, Jens-Christian F. 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 Z.
WebMaster
Yakima Herald-Republic Newspaper
removed_email_address@domain.invalid
509-577-7732


#19

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 Z.
WebMaster
Yakima Herald-Republic Newspaper
removed_email_address@domain.invalid
509-577-7732


#20

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