Forum: Ruby on Rails Newbie: Building conditions from form

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.
davewatts (Guest)
on 2005-11-30 10:56
(Received via mailing list)
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.
(http://wiki.rubyonrails.com/rails/pages/GettingSta...).
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/buildi...
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' %>

<p><label for="person_name">Name</label><br/>
<%= text_field 'person', 'name'  %></p>

<p><label for="person_city">City</label><br/>
<%= text_field 'person', 'city'  %></p>

<p><label for="person_state">State</label><br/>
<%= text_field 'person', 'state'  %></p>

<%= submit_tag "Search" %>

<%= end_form_tag %>
norman (Guest)
on 2005-11-30 13:26
(Received via mailing list)
Am Mittwoch, den 30.11.2005, 00:54 -0800 schrieb David W.:
> http://blog.teksol.info/articles/2005/10/31/buildi...
> "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
francois.beausoleil (Guest)
on 2005-11-30 17:18
(Received via mailing list)
Hello David !

2005/11/30, David W. <removed_email_address@domain.invalid>:
>      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 (Guest)
on 2005-12-01 02:09
(Received via mailing list)
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: <h1>Listing Found</h1>
2:
3: <p>
4:     <%= @person.name %><br />
5:     <%= @person.street1 %><br />
6:     <%= @person.street2 %><br />
7:     <%= @person.city %><br />

Thanks Again for your patience
norman (Guest)
on 2005-12-01 11:43
(Received via mailing list)
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.
Christer N. (Guest)
on 2005-12-01 12:58
How do you handle LIKE ?
norman (Guest)
on 2005-12-01 17:09
(Received via mailing list)
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
ezra (Guest)
on 2005-12-01 19:35
(Received via mailing list)
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
removed_email_address@domain.invalid
Christer N. (Guest)
on 2005-12-01 20:05
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
Jm M. (Guest)
on 2005-12-01 20:07
(Received via mailing list)
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/
brucebalmer (Guest)
on 2005-12-01 21:33
(Received via mailing list)
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.
norman (Guest)
on 2005-12-01 21:45
(Received via mailing list)
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 ;)
bruno.celeste (Guest)
on 2005-12-01 22:34
(Received via mailing list)
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:
Christer N. (Guest)
on 2005-12-01 22:40
Yeah, I would like to, but I'm an Ruby nuby. Ezra is looking at this.

Christer
ezra (Guest)
on 2005-12-01 23:43
(Received via mailing list)
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
removed_email_address@domain.invalid
davewatts (Guest)
on 2005-12-02 01:29
(Received via mailing list)
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
Christer N. (Guest)
on 2005-12-02 01:29
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.
ezra (Guest)
on 2005-12-02 02:21
(Received via mailing list)
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
removed_email_address@domain.invalid
Christer N. (Guest)
on 2005-12-02 10:29
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!")
ezra (Guest)
on 2005-12-02 19:25
(Received via mailing list)
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
> 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.