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?

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?

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

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' %>

conditions = ["price < ?", params[:query]] unless params[:query] == ''

Awesome! Works perfect. This is SQL language, right? Do you know
where I can find a reference to learn it?

Thanks,
JD

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. :wink:

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?

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

Kinda. 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.