thr3ads.net - Rails - Newbie: Building conditions from form [Nov 2005]

If this information is useful, please help other people find it:
Share via:

David Watts

2005-Nov-30 08:54 UTC

Newbie: Building conditions from form

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/GettingStartedWithRails).
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'' %>

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

2005-Nov-30 11:24 UTC

head link

Re: Newbie: Building conditions from form

Am Mittwoch, den 30.11.2005, 00:54 -0800 schrieb David
Watts:> 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/GettingStartedWithRails).
> 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
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
> 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 %>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Francois Beausoleil

2005-Nov-30 15:16 UTC

head link

Re: Newbie: Building conditions from form

Hello David !

2005/11/30, David Watts
<davewatts@pennysaverusa.net>:> 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
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 !
--
François Beausoleil
http://blog.teksol.info/

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

David Watts

2005-Dec-01 00:08 UTC

head link

Re: Newbie: Building conditions from form

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
> 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 !
> --
> François Beausoleil
> http://blog.teksol.info/
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

Norman Timmler

2005-Dec-01 09:42 UTC

head link

Re: Newbie: Building conditions from form

Am Mittwoch, den 30.11.2005, 16:08 -0800 schrieb David
Watts:> 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?
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.
> 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
> 
> > 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 !
> > --
> > François Beausoleil
> > http://blog.teksol.info/
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Christer Nilsson

2005-Dec-01 10:58 UTC

head link

Re: Newbie: Building conditions from form

How do you handle LIKE ?

-- 
Posted via http://www.ruby-forum.com/.

Norman Timmler

2005-Dec-01 15:05 UTC

head link

Re: Re: Newbie: Building conditions from form

Am Donnerstag, den 01.12.2005, 11:58 +0100 schrieb Christer
Nilsson:> 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 Zygmuntowicz

2005-Dec-01 17:28 UTC

head link

Re: Newbie: Building conditions from form

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 Zygmuntowicz
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org

Christer Nilsson

2005-Dec-01 18:05 UTC

head link

Re: Newbie: Building conditions from form

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

-- 
Posted via http://www.ruby-forum.com/.

jonathan Mcintire

2005-Dec-01 18:07 UTC

head link

BlueCloth - RedCloth : How to use?

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/

Bruce Balmer

2005-Dec-01 19:31 UTC

head link

Re: Re: Newbie: Building conditions from form

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.


On 1-Dec-05, at 11:05 AM, Christer Nilsson wrote:
> 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
>
> -- 
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

Norman Timmler

2005-Dec-01 19:44 UTC

head link

Re: Re: Newbie: Building conditions from form

Am Donnerstag, den 01.12.2005, 19:05 +0100 schrieb Christer
Nilsson:> 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
> 
Maybe you want to write a little plugin and share it with us? This would
be a nice contribute to the community ;)

Hogan, Brian P.

2005-Dec-01 20:28 UTC

head link

RE: Re: Newbie: Building conditions from form

Agreed.

-----Original Message-----
From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Bruce Balmer
Sent: Thursday, December 01, 2005 1:31 PM
To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: Re: [Rails] Re: Newbie: Building conditions from form


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.


On 1-Dec-05, at 11:05 AM, Christer Nilsson wrote:
> 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
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Bruno Celeste

2005-Dec-01 20:30 UTC

head link

Re: BlueCloth - RedCloth : How to use?

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:>
> 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?

Christer Nilsson

2005-Dec-01 20:40 UTC

head link

Re: Re: Newbie: Building conditions from form

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

Christer

-- 
Posted via http://www.ruby-forum.com/.

Ezra Zygmuntowicz

2005-Dec-01 21:36 UTC

head link

Re: Re: Newbie: Building conditions from form

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 Zygmuntowicz
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org

David Watts

2005-Dec-01 23:03 UTC

head link

Re: Newbie: Building conditions from form

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

On Dec 1, 2005, at 1:42 AM, Norman Timmler wrote:
> Am Mittwoch, den 30.11.2005, 16:08 -0800 schrieb David Watts:
>> 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?
>
> 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.
>
>> 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
>>
>>> 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 !
>>> --
>>> François Beausoleil
>>> http://blog.teksol.info/
>>> _______________________________________________
>>> Rails mailing list
>>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>> _______________________________________________
>> Rails mailing list
>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

Christer Nilsson

2005-Dec-01 23:29 UTC

head link

Newbie: Building conditions from form

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.


-- 
Posted via http://www.ruby-forum.com/.

Ezra Zygmuntowicz

2005-Dec-02 00:15 UTC

head link

Re: Newbie: Building conditions from form

On Dec 1, 2005, at 3:29 PM, Christer Nilsson 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.
>
> 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.
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.
>
> 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.
>
>
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 Zygmuntowicz
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org

Christer Nilsson

2005-Dec-02 08:29 UTC

head link

Newbie: Building conditions from form

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!")

-- 
Posted via http://www.ruby-forum.com/.

Ezra Zygmuntowicz

2005-Dec-02 17:22 UTC

head link

Re: Newbie: Building conditions from form

Cool stuff!
On Dec 2, 2005, at 12:29 AM, Christer Nilsson wrote:
> 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 <(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
>   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!")
>
> -- 
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-Ezra Zygmuntowicz
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org

Julian ''Julik'' Tarkhanov

2005-Dec-04 23:29 UTC

head link

Re: Re: Newbie: Building conditions from form

On 1-dec-2005, at 21:28, Hogan, Brian P. wrote:
> Agreed.
>
> -----Original Message-----
> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Bruce Balmer
> Sent: Thursday, December 01, 2005 1:31 PM
> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Subject: Re: [Rails] Re: Newbie: Building conditions from form
>
>
> 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.
It''s not about readability, SQL is very elegant and readable.

it''s about composing the needed query _programmatically_ with minimum  
fuss, adding conditions and operators
as they are needed, conditionally or in batches.

Whuc, in current Rails, IS clunky.

I think I''ve seen a Query plugin somewhere on the web but I just  
can''t trace it. Maybe it was announced on the list as well.
--
Julian ''Julik'' Tarkhanov
me at julik.nl

Rails - Nov 2005 - Newbie: Building conditions from form

Newbie: Building conditions from form

Re: Newbie: Building conditions from form

Re: Newbie: Building conditions from form

Re: Newbie: Building conditions from form

Re: Newbie: Building conditions from form

Re: Newbie: Building conditions from form

Re: Re: Newbie: Building conditions from form

Re: Newbie: Building conditions from form

Re: Newbie: Building conditions from form

BlueCloth - RedCloth : How to use?

Re: Re: Newbie: Building conditions from form

Re: Re: Newbie: Building conditions from form

RE: Re: Newbie: Building conditions from form

Re: BlueCloth - RedCloth : How to use?

Re: Re: Newbie: Building conditions from form

Re: Re: Newbie: Building conditions from form

Re: Newbie: Building conditions from form

Newbie: Building conditions from form

Re: Newbie: Building conditions from form

Newbie: Building conditions from form

Re: Newbie: Building conditions from form

Re: Re: Newbie: Building conditions from form