jamesdylangoldstein-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
2007-Apr-13 18:15 UTC
SQL syntax: conditions question for less than or equal to
When price is less than or equal to the price the user enters, display
it. I can''t seem to get this condition right:
conditions = ["price < ?", "%#{@params[:query]}%"] unless
@params[:query].nil?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Craig White
2007-Apr-13 19:16 UTC
Re: SQL syntax: conditions question for less than or equal to
On Fri, 2007-04-13 at 18:15 +0000, jamesdylangoldstein-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote:> When price is less than or equal to the price the user enters, display > it. I can''t seem to get this condition right: > > conditions = ["price < ?", "%#{@params[:query]}%"] unless > @params[:query].nil?---- perhaps I missed something here - especially seeing that this is a continuation thread but @params is deprecated and much of this seems unnecessary to me... conditions = ["price < ?", params[:query] unless params[:query].nil but I also wonder if params[:query] is an actual value... -- Craig White <craig-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org> --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
jamesdylangoldstein-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
2007-Apr-13 19:39 UTC
Re: SQL syntax: conditions question for less than or equal to
Well, I tried that. Error because of a missing bracket, added a bracket: NoMethodError in InventoryController#listprice undefined method `nil'' for "5000":String So, I might be ahead of my abilities. But I just created two AJAX textboxes and linked them, one for make, the other for model. And they work great. So I added another and did the small details and then I thought I would just need to change the conditions. Thus giving me three textboxes, one for make, model and price. And you can narrow your search with each box. Enter your maximum price and it eliminates vehicles that are more expensive than that. Thanks, JD On Apr 13, 3:16 pm, Craig White <c...-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org> wrote:> On Fri, 2007-04-13 at 18:15 +0000, jamesdylangoldst...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote: > > When price is less than or equal to the price the user enters, display > > it. I can''t seem to get this condition right: > > > conditions = ["price < ?", "%#{@params[:query]}%"] unless > > @params[:query].nil? > > ---- > perhaps I missed something here - especially seeing that this is a > continuation thread but @params is deprecated and much of this seems > unnecessary to me... > > conditions = ["price < ?", params[:query] unless params[:query].nil > > but I also wonder if params[:query] is an actual value... > > -- > Craig White <c...-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org>--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
jamesdylangoldstein-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
2007-Apr-13 19:46 UTC
Re: SQL syntax: conditions question for less than or equal to
if you are wondering what my code is:
Inventorycontroller.rb
...
def listprice
vehicles_per_page = 10
sort = case @params[''sort'']
when "make" then "make"
when "model" then "model"
when "price" then "price"
when "make_reverse" then "make DESC"
when "model_reverse" then "model DESC"
when "price_reverse" then "price DESC"
end
conditions = ["price < ?", params[:query]] unless
params[:query].nil
@total = Vehicle.count(:conditions => conditions)
@vehicles_pages, @vehicles = paginate :vehicles, :order =>
sort, :conditions => conditions, :per_page => vehicles_per_page
if request.xml_http_request?
render :partial => "vehicles_list", :layout => false
end
end
list.rhtml
...
<form name=priceform" action=""
style="display:inline;">
<label for="vehicle_price">Filter on vehicle
price : </label>
<%= text_field_tag("query",
@params[''query''], :size
=> 10) %>
</form>
<%= image_tag("spinner.gif",
:align => ''absmiddle'',
:border=> 0,
:id => "spinner",
:style=>"display: none;" )
%>
</p>
<%= observe_field ''query'', :frequency => 2,
:update => ''table'',
:before =>
"Element.show(''spinner'')",
:success =>
"Element.hide(''spinner'')",
:url => {:action =>
''listprice''},
:with => ''query'' %>
On Apr 13, 3:16 pm, Craig White
<c...-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org>
wrote:> On Fri, 2007-04-13 at 18:15 +0000,
jamesdylangoldst...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote:
> > When price is less than or equal to the price the user enters, display
> > it. I can''t seem to get this condition right:
>
> > conditions = ["price < ?",
"%#{@params[:query]}%"] unless
> > @params[:query].nil?
>
> ----
> perhaps I missed something here - especially seeing that this is a
> continuation thread but @params is deprecated and much of this seems
> unnecessary to me...
>
> conditions = ["price < ?", params[:query] unless
params[:query].nil
>
> but I also wonder if params[:query] is an actual value...
>
> --
> Craig White <c...-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Craig White
2007-Apr-13 22:15 UTC
Re: SQL syntax: conditions question for less than or equal to
conditions = ["price < ?", params[:query]] unless params[:query] == ''''> On Fri, 2007-04-13 at 19:46 +0000, jamesdylangoldstein-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote: > if you are wondering what my code is: > > Inventorycontroller.rb > ... > def listprice > > vehicles_per_page = 10 > > sort = case @params[''sort''] > when "make" then "make" > when "model" then "model" > when "price" then "price" > when "make_reverse" then "make DESC" > when "model_reverse" then "model DESC" > when "price_reverse" then "price DESC" > end > > conditions = ["price < ?", params[:query]] unless > params[:query].nil > > @total = Vehicle.count(:conditions => conditions) > @vehicles_pages, @vehicles = paginate :vehicles, :order => > sort, :conditions => conditions, :per_page => vehicles_per_page > > if request.xml_http_request? > render :partial => "vehicles_list", :layout => false > end > > end > > list.rhtml > ... > <form name=priceform" action="" style="display:inline;"> > <label for="vehicle_price">Filter on vehicle > price : </label> > <%= text_field_tag("query", @params[''query''], :size > => 10) %> > </form> > > <%= image_tag("spinner.gif", > :align => ''absmiddle'', > :border=> 0, > :id => "spinner", > :style=>"display: none;" ) %> > </p> > > <%= observe_field ''query'', :frequency => 2, > :update => ''table'', > :before => "Element.show(''spinner'')", > :success => "Element.hide(''spinner'')", > :url => {:action => ''listprice''}, > :with => ''query'' %> > > On Apr 13, 3:16 pm, Craig White <c...-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org> wrote: > > On Fri, 2007-04-13 at 18:15 +0000, jamesdylangoldst...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote: > > > When price is less than or equal to the price the user enters, display > > > it. I can''t seem to get this condition right: > > > > > conditions = ["price < ?", "%#{@params[:query]}%"] unless > > > @params[:query].nil? > > > > ---- > > perhaps I missed something here - especially seeing that this is a > > continuation thread but @params is deprecated and much of this seems > > unnecessary to me... > > > > conditions = ["price < ?", params[:query] unless params[:query].nil > > > > but I also wonder if params[:query] is an actual value... > > > > -- > > Craig White <c...-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org> > > > >-- Craig White <craig-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org> --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
jamesdylangoldstein-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
2007-Apr-13 23:38 UTC
Re: SQL syntax: conditions question for less than or equal to
Awesome! Works perfect. This is SQL language, right? Do you know where I can find a reference to learn it? Thanks, JD On Apr 13, 6:15 pm, Craig White <c...-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org> wrote:> conditions = ["price < ?", params[:query]] unless params[:query] == '''' > > > > > On Fri, 2007-04-13 at 19:46 +0000, jamesdylangoldst...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote: > > if you are wondering what my code is: > > > Inventorycontroller.rb > > ... > > def listprice > > > vehicles_per_page = 10 > > > sort = case @params[''sort''] > > when "make" then "make" > > when "model" then "model" > > when "price" then "price" > > when "make_reverse" then "make DESC" > > when "model_reverse" then "model DESC" > > when "price_reverse" then "price DESC" > > end > > > conditions = ["price < ?", params[:query]] unless > > params[:query].nil > > > @total = Vehicle.count(:conditions => conditions) > > @vehicles_pages, @vehicles = paginate :vehicles, :order => > > sort, :conditions => conditions, :per_page => vehicles_per_page > > > if request.xml_http_request? > > render :partial => "vehicles_list", :layout => false > > end > > > end > > > list.rhtml > > ... > > <form name=priceform" action="" style="display:inline;"> > > <label for="vehicle_price">Filter on vehicle > > price : </label> > > <%= text_field_tag("query", @params[''query''], :size > > => 10) %> > > </form> > > > <%= image_tag("spinner.gif", > > :align => ''absmiddle'', > > :border=> 0, > > :id => "spinner", > > :style=>"display: none;" ) %> > > </p> > > > <%= observe_field ''query'', :frequency => 2, > > :update => ''table'', > > :before => "Element.show(''spinner'')", > > :success => "Element.hide(''spinner'')", > > :url => {:action => ''listprice''}, > > :with => ''query'' %> > > > On Apr 13, 3:16 pm, Craig White <c...-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org> wrote: > > > On Fri, 2007-04-13 at 18:15 +0000, jamesdylangoldst...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote: > > > > When price is less than or equal to the price the user enters, display > > > > it. I can''t seem to get this condition right: > > > > > conditions = ["price < ?", "%#{@params[:query]}%"] unless > > > > @params[:query].nil? > > > > ---- > > > perhaps I missed something here - especially seeing that this is a > > > continuation thread but @params is deprecated and much of this seems > > > unnecessary to me... > > > > conditions = ["price < ?", params[:query] unless params[:query].nil > > > > but I also wonder if params[:query] is an actual value... > > > > -- > > > Craig White <c...-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org> > > -- > Craig White <c...-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org>--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Sam Smoot
2007-Apr-14 00:21 UTC
Re: SQL syntax: conditions question for less than or equal to
On Apr 13, 5:15 pm, Craig White <c...-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org> wrote:> conditions = ["price < ?", params[:query]] unless params[:query] == ''''What if :query isn''t passed? A better option would be: conditions = [ ''price < ?'', params[:query] ] unless params[:query].blank? Note: Ruby methods that return boolean values typically end in a question-mark. It''s pretty. That''s why the earlier example with .nil? didn''t work. The question mark wasn''t referring to a question, it was part of the code. ;-) Object#blank? is a helper method that Rails adds that compares against Object#nil? and Object#empty?, so when dealing with form values, it''s typically the preferred method. Even better than my suggestion above might be to validate the value, otherwise you could generate a query that makes no sense, or one that is less efficient because it involves coersion of the parameter (from a VARCHAR to a DECIMAL for example). So perhaps: require ''bigdecimal'' class String def to_decimal self =~ /([\d\,]+(\.\d*)?|[\d\,]*\.\d+)/ $1.nil? ? nil : BigDecimal($1.gsub(/\,/, '''')) end end conditions = [ ''price < ?'', params[:query].to_decimal ] unless params[:query].blank? || params[:query].to_decimal.nil? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Craig White
2007-Apr-14 00:24 UTC
Re: SQL syntax: conditions question for less than or equal to
Um - no, it''s Rails Active Record which sort of does the SQL magic for
you which is the sexy part of Rails.
You might want to learn more about the Ruby language and the ''Pick
Axe''
book is the bible for Ruby ("Programming Ruby by Dave Thomas")
Other books also of extreme usefulness would be ''Agile Web Development
with Rails'' by Dave Thomas and David Heinemeier Hanson and
''Ruby for
Rails'' by David Black
All authors frequent the list and all of these titles are available in
dead tree form (most any bookseller or direct from the publishers) or
from the publishers web site
On Fri, 2007-04-13 at 23:38 +0000,
jamesdylangoldstein-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
wrote:> Awesome! Works perfect. This is SQL language, right? Do you know
> where I can find a reference to learn it?
>
> Thanks,
> JD
>
> On Apr 13, 6:15 pm, Craig White
<c...-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org> wrote:
> > conditions = ["price < ?", params[:query]] unless
params[:query] == ''''
> >
> >
> >
> > > On Fri, 2007-04-13 at 19:46 +0000,
jamesdylangoldst...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote:
> > > if you are wondering what my code is:
> >
> > > Inventorycontroller.rb
> > > ...
> > > def listprice
> >
> > > vehicles_per_page = 10
> >
> > > sort = case @params[''sort'']
> > > when "make" then "make"
> > > when "model" then "model"
> > > when "price" then "price"
> > > when "make_reverse" then "make
DESC"
> > > when "model_reverse" then "model
DESC"
> > > when "price_reverse" then "price
DESC"
> > > end
> >
> > > conditions = ["price < ?", params[:query]]
unless
> > > params[:query].nil
> >
> > > @total = Vehicle.count(:conditions => conditions)
> > > @vehicles_pages, @vehicles = paginate :vehicles, :order
=>
> > > sort, :conditions => conditions, :per_page =>
vehicles_per_page
> >
> > > if request.xml_http_request?
> > > render :partial => "vehicles_list", :layout
=> false
> > > end
> >
> > > end
> >
> > > list.rhtml
> > > ...
> > > <form name=priceform" action=""
style="display:inline;">
> > > <label
for="vehicle_price">Filter on vehicle
> > > price : </label>
> > > <%= text_field_tag("query",
@params[''query''], :size
> > > => 10) %>
> > > </form>
> >
> > > <%= image_tag("spinner.gif",
> > > :align =>
''absmiddle'',
> > > :border=> 0,
> > > :id =>
"spinner",
> > > :style=>"display:
none;" ) %>
> > > </p>
> >
> > > <%= observe_field ''query'', :frequency =>
2,
> > > :update =>
''table'',
> > > :before =>
"Element.show(''spinner'')",
> > > :success =>
"Element.hide(''spinner'')",
> > > :url => {:action =>
''listprice''},
> > > :with =>
''query'' %>
> >
> > > On Apr 13, 3:16 pm, Craig White
<c...-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org> wrote:
> > > > On Fri, 2007-04-13 at 18:15 +0000,
jamesdylangoldst...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote:
> > > > > When price is less than or equal to the price the user
enters, display
> > > > > it. I can''t seem to get this condition right:
> >
> > > > > conditions = ["price < ?",
"%#{@params[:query]}%"] unless
> > > > > @params[:query].nil?
> >
> > > > ----
> > > > perhaps I missed something here - especially seeing that
this is a
> > > > continuation thread but @params is deprecated and much of
this seems
> > > > unnecessary to me...
> >
> > > > conditions = ["price < ?", params[:query]
unless params[:query].nil
> >
> > > > but I also wonder if params[:query] is an actual value...
> >
> > > > --
> > > > Craig White
<c...-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org>
> >
> > --
> > Craig White <c...-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org>
>
>
> >
--
Craig White <craig-CnJ8jr4MGtxl57MIdRCFDg@public.gmane.org>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Sam Smoot
2007-Apr-14 01:14 UTC
Re: SQL syntax: conditions question for less than or equal to
On Apr 13, 6:38 pm, "jamesdylangoldst...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org" <jamesdylangoldst...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Awesome! Works perfect. This is SQL language, right? Do you know > where I can find a reference to learn it? > > Thanks, > JDKinda. It''s a mixture. ActiveRecord depends heavily on SQL certainly. You''re not going to get very far without it. I''m not quite sure of a good resource to learn it. Perhaps the MySQL documentation (assuming that''s your database). The query generated in Craig''s example might look like this for example: SELECT * FROM products WHERE price > ''20.0'' That forces the database to convert the VARCHAR value ''20.0'' into the DECIMAL value 20.0 though. Which under certain conditions could end up in execution times many times slower. So assuming ActiveRecord properly represents decimals during query generation and doesn''t just turn it into a string itself, the later String#to_decimal example I posted should get around that. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---