Newbie: Building conditions from form

davewatts · November 30, 2005, 9:56am

Hi All,

I am still trying to get the hang of passing information to controller.
I want to extend the rails getting started tutorial for searching.
(Peak Obsession).
The index page would be the main search form passing the parameters
to a view
controller and displayed with a view.rhtml page.

I have started with something from a blog that looked promising.
http://blog.teksol.info/articles/2005/10/31/building-the-sql-where-
clause-dynamically-in-rails

Here is what I have started with but not sure how to get the values into
the conditions correctly.

Thanks for any suggestions

Processing FriendsController#view (for 127.0.0.1 at 2005-11-30
00:14:11) [POST]
Parameters: {“commit”=>“Search”, “action”=>“view”,
“controller”=>“friends”, “person”=>{“city”=>“”, “name”=>“Dave”,
“state”=>“”}}

friends_controller.rb

def view
#initialize the conditions array: [‘1=1’]. This is to prevent a
problem later on when Rails
#evaluates the conditions option to find .condition. (from blog)
conditions = [‘1=1’]
puts @params[:person][‘name’] if @params[:person][‘name’] #
works fine get name
conditions << ‘name = :name’ if @params[:person][‘name’] # what
is needed here?
conditions << ‘city = :city’ if @params[:person][‘city’]
conditions << ‘state = :state’ if @params[:person][‘state’]
@person = Person.find(:first, :conditions => [conditions.join(’
AND '), params])
puts conditions
end

index.rhtml

<%= start_form_tag :action => ‘view’ %>

Name
<%= text_field 'person', 'name' %>

City
<%= text_field 'person', 'city' %>

State
<%= text_field 'person', 'state' %>

<%= submit_tag “Search” %>

<%= end_form_tag %>

davewatts · November 30, 2005, 12:26pm

Am Mittwoch, den 30.11.2005, 00:54 -0800 schrieb David W.:

http://blog.teksol.info/articles/2005/10/31/building-the-sql-where-
“controller”=>“friends”, “person”=>{“city”=>“”, “name”=>“Dave”,
works fine get name
conditions << ‘name = :name’ if @params[:person][‘name’] # what
is needed here?
conditions << ‘city = :city’ if @params[:person][‘city’]
conditions << ‘state = :state’ if @params[:person][‘state’]
@person = Person.find(:first, :conditions => [conditions.join(’
AND '), params])
puts conditions
end

This is untested:

def view
conditions = [[]]
params[:person].each do |key_value|
key, value = key_value
unless value.blank?
conditions.first << “#{key} = ?”
conditions << value
end
end
conditions.first.join(’ AND ')
@person = conditions.size > 1 ? Person.find(:first, :conditions =>
conditions) : nil
end

davewatts · November 30, 2005, 4:18pm

Hello David !

2005/11/30, David W. [email protected]:

 conditions << 'state = :state' if @params[:person]['state']
 @person = Person.find(:first, :conditions => [conditions.join('

AND '), params])
puts conditions
end

This is your error:
@person = Person.find(:first, :conditions => [conditions.join(’ AND '),
params])

You need to do this:
@person = Person.find(:first, :conditions => [conditions.join(’ AND
'), params[:person]])

Notice I used params[:person], and not plain params. Rails is
attempting to search for a key by name :state, but there are none in
params. It is stored as params[:person][:state].

Also, you might get an error because you have too many parameters in
the params Hash vs what you are using in the conditions. You’ll have
to initialize a new Hash with just the values you need:

def view
conditions = [‘1=1’]
values = Hash.new

if params[:person][:city] then
conditions << ‘city = :city’
values[:city] = params[:person][:city]
end

@people = Person.find(:all, :conditions => [conditions.join(’ AND '),
values])
end

BTW, that blog you refer to is mine Happy to see it is of some
help to someone.

Bye !

davewatts · December 1, 2005, 10:43am

Am Mittwoch, den 30.11.2005, 16:08 -0800 schrieb David W.:

What is needed to remove the blank conditions?
I refere you to my post from yesterday to this subject. Following method
handles your problem. By the way, this is a little bit more generic than
your approach.

def view
conditions = [[]]
params[:person].each do |key_value|
key, value = key_value
unless value.blank?
conditions.first << “#{key} = ?”
conditions << value
end
end
conditions.first.join(’ AND ')
@person = conditions.size > 1 ? Person.find(:first, :conditions =>
conditions) : nil
end

Try to analyse and understand it. You will see.

davewatts · December 1, 2005, 11:58am

How do you handle LIKE ?

davewatts · December 1, 2005, 1:09am

Thanks,

The list has been very kind and patient with my questions.

Now for the but…

I was able to get the search to work for one value (params[:person]
[:name]).
As soon as I added a second one it blows up doooh…

What is needed to remove the blank conditions?

What I have now is:

 def view
 #initialize the conditions array: ['1=1'].
 conditions = ['1=1']
 values = Hash.new

 if params[:person][:name] then
   conditions << 'name = :name'
   values[:name] = params[:person][:name]
 end
 if params[:person][:city] then
   conditions << 'city = :city'
   values[:city] = params[:person][:city]
end

 puts conditions
@person = Person.find(:first, :conditions => [conditions.join('

AND '), values])
end

Giving me:
Which makes sense it should not find anything

Parameters: {“commit”=>“Search”, “action”=>“view”,
“controller”=>“friends”, “person”=>{“city”=>"", “name”=>“dave”,
“state”=>""}}
Person Load (0.001174) SELECT * FROM people WHERE (1=1 AND name
= ‘dave’ AND city = ‘’) LIMIT 1
Rendering friends/view

ActionView::TemplateError (You have a nil object when you didn’t
expect it!
The error occured while evaluating nil.name) on line #4 of app/views/
friends/view.rhtml:
1:

Listing Found

2:
3:

4: <%= @person.name %>

5: <%= @person.street1 %>

6: <%= @person.street2 %>

7: <%= @person.city %>

Thanks Again for your patience

davewatts · December 1, 2005, 4:09pm

Am Donnerstag, den 01.12.2005, 11:58 +0100 schrieb Christer N.:

How do you handle LIKE ?

See this line ‘conditions.first << “#{key} = LIKE ?”’:

def view
conditions = [[]]
params[:person].each do |key_value|
key, value = key_value
unless value.blank?
conditions.first << “#{key} LIKE ?”
conditions << value
end
end
conditions.first.join(’ AND ')
@person = conditions.size > 1 ? Person.find(:first, :conditions =>
conditions) : nil
end

You could even do a more open search, if you surround the search values
with ‘%’:

def view
conditions = [[]]
params[:person].each do |key_value|
key, value = key_value
unless value.blank?
conditions.first << “#{key} LIKE ?”
conditions << “%#{value}%”
end
end
conditions.first.join(’ AND ')
@person = conditions.size > 1 ? Person.find(:first, :conditions =>
conditions) : nil
end

davewatts · December 1, 2005, 6:35pm

I have just written a better (IMHO) way to build the where clause
dynamically. The following Where class will hopefully help you out:

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”]

Cheers-

-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
[email protected]

davewatts · December 1, 2005, 7:07pm

I downloaded the gems to both of these. But, I’m not
sure how to get them working in my App. Has anyone
used either of these?

Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/

davewatts · December 1, 2005, 7:05pm

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 is 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

davewatts · December 1, 2005, 8:33pm

I find the apparently common dislike of SQL surprising. To me, SQL is
a language much like css. Very compact. Very logical (therefore easy
to retain) and very powerful.

davewatts · December 1, 2005, 8:45pm

Am Donnerstag, den 01.12.2005, 19:05 +0100 schrieb Christer N.:

And remember, all 2^7 = 128 combinations should be possible!
My long term goal is to NEVER see SQL. This is one of the problems on
the journey.

Christer

Maybe you want to write a little plugin and share it with us? This would
be a nice contribute to the community

davewatts · December 1, 2005, 9:40pm

Yeah, I would like to, but I’m an Ruby nuby. Ezra is looking at this.

Christer

davewatts · December 1, 2005, 9:34pm

Hi,
In your view, if you want to use textile (redcloth):
<%= textilize(@page.description) %>

And if you want to use markdown (bluecloth), use:
<%= markdown(@page.description) %>

2005/12/1, jonathan Mcintire:

davewatts · December 1, 2005, 10:43pm

OK, here’s my attempt to support more operators. What do you think?

class Where

def initialize(options=[])
@opts = options
@opts.each do |triplet|
instance_variable_set(“@#{triplet[0].to_sym}”, triplet[2])
end
end

def build_where_clause
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 # End Where

USAGE

Pass in your query params in an array containing nested arrays with

3 items in each

nested array.

a = Where.new([[:project,“=”, 3], [:month, “>=”, 7], [:comment,
“LIKE”, “%ruby%”]])
p a.build_where_clause

=> [“project = ? AND month >= ? AND comment LIKE ?”, 3, 7, “%ruby%”]

So the format of the Where class constructor needs an array with

these specs:

b = Where.new([ [:column_name1, “LIKE”, “%value%”], [:column_name2,
“>=”, 8], [:column_name3, “=”, 12], [:date, “<=”, ‘2006-12-0’ ] ])
p b.build_where_clause

=> ["column_name1 LIKE ? AND column_name2 >= ? AND column_name3 = ?

AND date <= ?", “%value%”, 8, 12, “2006-12-0”]

So you can pass as many columns as you like using whatever operator

you like =, >, <, >=, <=, LIKE

and whatever esle it doesn’t really care as long as its valid SQL.

I still haven’t figured out how to use

OR instead of AND intermixed in the same query but I will keep

working on it. Once I get it to be much

more robust I will make it a small plug in.

Its a fun little class but is it really any shorter than writing

the :conditions yourself?

Thoughts?

Cheers-

-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
[email protected]

davewatts · December 2, 2005, 12:29am

Ezra, I think you are on the right track. Thus will save me a lot of
lines.

Some thoughts about the and/or problem.

I’m not sure about the Ruby syntax, but consider this example

Where.new ( [:or, [:and, [x=1], [y=2], [z=3]], [w=4]] )
( x=1 translates to your format [:x, “=”, 1] )

which will evaluate to
(x=1 and y=2 and z=3) or w=4.

If one or more of the values is nil (not given), there will be a
simplification.
1 is nil: (y=2 and z=3) or w=4
2 is nil: (x=1 and z=3) or w=4
3 is nil: (x=1 and y=2) or w=4
4 is nil: x=1 and y=2 and y=3
etc.

Another way of doing this would be to introduce 1=1 or 1=0
1 is nil: (1=1 and y=2 and z=3) or w=4
2 is nil: (x=1 and 1=1 and z=3) or w=4
3 is nil: (x=1 and y=2 and 1=1) or w=4
4 is nil: (x=1 and y=2 and y=3) or 1=0

Alternative syntax:
or(and(x=1,y=2,z=3),w=4)

There will be some recursion necessary to produce the statement.

Is there a better way of expressing this in Ruby ?

Maybe “=”, “>=”, “LIKE”, etc should be functions.
Example
like(:comment,"%ruby%")

I see no problem with the implementation, my question is, how to express
the condition clause in a human readable way, using Ruby.

davewatts · December 2, 2005, 12:29am

Hi Norman,

Thank you very much. I have actually been working on understanding
the code (newbie moment) you used. Below is what I finally got to
work for me.
Not very pretty, considering just learning ruby/rails. Let me know if
there is a better way to do this.

Again, thank you it really helped

Dave

 def view
   myconditions = []
   params[:person].each do |key_value|
     key, value = key_value
     unless value.nil? || value.blank?
       myconditions << "#{key} = '#{value}'"
     end
   end
   conditions = myconditions.join(' AND ')
   puts myconditions.size
   puts conditions
   @person = conditions.size >= 1 ? Person.find

(:first, :conditions => conditions) : nil
if @person == nil
flash[:notice] = ‘No records found.’
redirect_to :action => ‘index’
end
end

davewatts · December 2, 2005, 1:21am

On Dec 1, 2005, at 3:29 PM, Christer N. wrote:

Ezra, I think you are on the right track. Thus will save me a lot of
lines.

Some thoughts about the and/or problem.

I’m not sure about the Ruby syntax, but consider this example

Where.new ( [:or, [:and, [x=1], [y=2], [z=3]], [w=4]] )
( x=1 translates to your format [:x, “=”, 1] )

What about something like this:
Where.new( {:and = > [ [x=1], [y=2], [z=3] ], :or => [ [w=4],
[a=9] ] } )

I don’t think I am going to try to support every combination
possible. By the time it would work with all kinds of grouped and’s
and or’s you might as well write a custom query yourself. But lets
try to support the most common needs and make those easy.

etc.
This already works. If the value of one of the columns is nil or
empty string after a to_s method then it will be left out of the
statement all together.

There will be some recursion necessary to produce the statement.

Alright, I’m with you. Let me try to think of a small DSL to describe
the where clause in a more human readable way

-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
[email protected]

davewatts · December 2, 2005, 9:29am

Ezra, this is just a first approximation. No handling of numbers. No
handling of sql inject. Could you please give me a hint?

def zand(*arg)
arg.delete(nil)
arg.join(" and ")
end

def zor(*arg)
arg.delete(nil)
arg.nil? ? nil : “(#{arg.join(” or “)})”
end

def eq(a,b)
b.nil? ? nil : “#{a}=’#{b}’”
end

def lt(a,b)
b.nil? ? nil : “#{a}<=’#{b}’”
end

def gt(a,b)
b.nil? ? nil : “#{a}>=’#{b}’”
end

def zin(a,b)
b.empty? ? nil : “#{a} in (#{b.join(”,")})"
end

def between(a,b,c)
zand(gt(a,b),lt(a,c))
end

def like(a,b)
case b
when b.nil?, “%”, “%%”
nil
else
“#{a} like ‘#{b}’”
end
end

def assert(a,b)
a==b ? print(".") : print("\nexpected: #{a}\nactual: #{b}\n")
end

assert “price>=‘500’ and price<=‘1000’”, between(“price”,“500”,“1000”)
assert “price>=‘100’ and price<=‘500’”, zand(gt(“price”,“100”),
lt(“price”,“500”))
assert “rooms in (1,2,3)”, zin(“rooms”,[1,2,3])
assert nil, zin(“rooms”,[])
assert “size>=‘100’”, gt(“size”,“100”)
assert “size<=‘500’”, lt(“size”,“500”)
assert “price<=‘500’”, zand(gt(“price”,nil), lt(“price”,“500”))
assert “”, zand(gt(“price”,nil), lt(“price”,nil))

size = zand(gt(“size”,“100”), lt(“size”,“150”))
price = zand(gt(“price”,nil), lt(“price”,“500”))
city = eq(“city”,“Berlin”)
assert “size>=‘100’ and size<=‘150’ and (price<=‘500’ or
city=‘Berlin’)”,zand(size,zor(price,city))

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”,"%%")

print("\nReady!")

davewatts · December 2, 2005, 6:25pm

Cool stuff!
On Dec 2, 2005, at 12:29 AM, Christer N. wrote:

arg.nil? ? nil : "(#{arg.join(" or ")})"
end

def eq(a,b)
b.nil? ? nil : “#{a}=‘#{b}’”
end

def lt(a,b)
b.nil? ? nil : “#{a}<=‘#{b}’”
end

 def <(a,b)
    b.nil? ? nil : "#{a}<='#{b}'"
 end

def gt(a,b)
b.nil? ? nil : “#{a}>=‘#{b}’”
end

 def >(a,b)
   b.nil? ? nil : "#{a}>='#{b}'
 end

I have some other code I wrote last night as well. Let me clean it up
and I’ll show it to you.

-Ezra

when b.nil?, "%", "%%"
assert “price>=‘500’ and price<=‘1000’”, between(“price”,“500”,“1000”)
price = zand(gt(“price”,nil), lt(“price”,“500”))
print(“\nReady!”)

–

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
[email protected]