Re: Ajax Live Search with MULTIPLE search criteria

Hm, looks interesting, thanks for the link. But that means I will have to convince my hosting company to install the plug-in too, right?

Re: Ajax Live Search with MULTIPLE search criteria

PixelLover wrote:

Hm, looks interesting, thanks for the link. But that means I will have to convince my hosting company to install the plug-in too, right?

I believe you can install it in vendor/plugins in your rails project.

Railscasts - Free Ruby on Rails Screencasts

Re: Ajax Live Search with MULTIPLE search criteria

Oh sorry, I saw your new post only just now. I've implemented your code and it gives me the following error message:

Mysql::Error: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1: SELECT count(*) AS count_all FROM vehicles WHERE ()

Not funny, huh?

Re: Ajax Live Search with MULTIPLE search criteria

I really should test code before I post it but I'm too lazy. hmm

Try this:

def self.search_conditions(make, style, min_price, max_price)
  conditions = ['1=1']
  conditions << "make = #{sanitize(make)}" unless make.blank?
  conditions << "style = #{sanitize(style)}" unless style.blank?
  conditions << "price >= #{min_price.to_f}" unless min_price.blank?
  conditions << "price <= #{max_price.to_f}" unless max_price.blank?
  conditions.join ' and '
end

Railscasts - Free Ruby on Rails Screencasts

Re: Ajax Live Search with MULTIPLE search criteria

Just for the record, the following code works:

  def self.search_conditions(make, style)
    conditions = ['1=1']
    conditions << "make = '#{make}'" unless make.blank?
    conditions << "style = '#{style}'" unless style.blank?
    conditions.join ' and '
  end

Unless the SQL starts with '1=1', an SQL error will be thrown. So it's absolutely vital to put it!!
The devil seems to be in the min_price, max_price variables. So I'll try to find a way to solve that problem.

Re: Ajax Live Search with MULTIPLE search criteria

ryanb wrote:

I really should test code before I post it but I'm too lazy. hmm

Try this:

def self.search_conditions(make, style, min_price, max_price)
  conditions = ['1=1']
  conditions << "make = #{sanitize(make)}" unless make.blank?
  conditions << "style = #{sanitize(style)}" unless style.blank?
  conditions << "price >= #{min_price.to_f}" unless min_price.blank?
  conditions << "price <= #{max_price.to_f}" unless max_price.blank?
  conditions.join ' and '
end

That's a slick way to construct a query string ryan.  I'll have to use that.

Re: Ajax Live Search with MULTIPLE search criteria

Yes, it is very slick. The final code as it stands right now (courtesy of RyanB) is:

Controller:

def cars
    conditions = Vehicle.search_conditions(params[:make], params[:style], params[:min_price], params[:max_price])

    @vehicle_pages, @vehicles = paginate :vehicles, :conditions => conditions, :per_page => 5
    @total = @vehicle_pages.item_count

    render :layout => false if request.xml_http_request?
  end


Model:
[code=] def self.search_conditions(make, style, min_price, max_price)
    conditions = ['1=1'] #VITAL!
    conditions << "make = '#{make}'" unless make.blank?
    conditions << "style = '#{style}'" unless style.blank?
    conditions << "price >= #{min_price}" unless min_price.blank?
    conditions << "price <= #{max_price}" unless max_price.blank?
    conditions.join ' and '
end
#By the way, there's no need to add sanitize() or type-casts here (it only works without).

#The contents of the dropdown boxes are derived from a number of hash constants in the Model (not too pretty, but it works):
MINPRICE = [
    [ "over

Last edited by PixelLover (2006-08-17 09:43:06)

Re: Ajax Live Search with MULTIPLE search criteria

Have you tried just adding it to the beginning of the options array for each field? For your min_price field it could look like this:

[code=ruby]MINPRICE = [
    [ "No Minimum Price", '' ], # Notice the blank value
    [ "over

Railscasts - Free Ruby on Rails Screencasts

Re: Ajax Live Search with MULTIPLE search criteria

Won't work. I've already tried all possible combinations, including ' ', " ", and 0.
Nothing changes, not even an error message pops up... :-(

Maybe the controller has to be changed...?

Last edited by PixelLover (2006-08-17 11:18:00)

Re: Ajax Live Search with MULTIPLE search criteria

PixelLover wrote:

Won't work. I've already tried all possible combinations, including ' ', " ", and 0.
Nothing changes, not even an error message pops up... :-(

Make sure no space exists between the quotes. It should be just "" because that is considered "blank".

Edit: Hmm, nevermind. I just tested it and the space is considered blank too:

''.blank?
#=> true
' '.blank?
#=> true
0.blank?
#=> false

Maybe look in the development logs and see what the generated SQL query is.

Last edited by ryanb (2006-08-17 11:29:04)

Railscasts - Free Ruby on Rails Screencasts

Re: Ajax Live Search with MULTIPLE search criteria

I am still not happy with my search function. I have found a nice way to include a default value in each of the dropdown boxes (I really don't think there's another way to handle this):

[code=]MINPRICE = [
    [ "No Min Price", 0 ], # will search for all prices greater than 0
    [ "over

Last edited by PixelLover (2006-08-21 06:42:01)

Re: Ajax Live Search with MULTIPLE search criteria

You could do:

["All Styles", '%']

And then change the SQL search to do a LIKE search instead of just equals:

conditions << "style LIKE '#{style}'" unless style.blank?

Last edited by ryanb (2006-08-21 10:13:33)

Railscasts - Free Ruby on Rails Screencasts

Re: Ajax Live Search with MULTIPLE search criteria

Ryan!! Thank you!!!

You've made me a very happy man!!

The search function seems perfect now. You can see it for yourself at my site.

The controller code I am using:

  def self.search_conditions(make, style, min_price, max_price)
    conditions = ['1=1'] #VITAL!
    conditions << "make LIKE '#{make}'" unless make.blank?
    conditions << "style LIKE '#{style}'" unless style.blank?
    conditions << "price >= #{min_price}" unless min_price.blank?
    conditions << "price <= #{max_price}" unless max_price.blank?
    conditions.join ' and '
  end

(It's important to use 'LIKE', not '=' or anything else.)

The hashes for Strings should look like this:

  STYLE = [
    [ "All Body Styles",'%' ],
    [ "Convertible",    "Convertible" ],
    [ "Coupe",          "Coupe" ],
    [ "Estate",         "Estate" ],
    [ "Hatchback",      "Hatchback" ],
    [ "Off Road",       "Off Road" ],
    [ "People Carrier", "People Carrier" ],
    [ "Saloon",         "Saloon" ],
  ].freeze

(Note the '%' in the second line. Without it, nothing will work!!)

Numerical hashes should look like this:

[code=]  MINPRICE = [
    [ "No Min Price", 0 ],
    [ "over

Last edited by PixelLover (2006-08-21 10:45:10)

Re: Ajax Live Search with MULTIPLE search criteria

Hi,
I am still working on this database search thing. Can anybody point me to a good tutorial on how to implement database searches with multiple search criteria through form input? That would be fantastic!

Re: Ajax Live Search with MULTIPLE search criteria

Hi Ryanb,
It seems the AJAX search function is too heavy-weight for the database. At least the application crashes if the search function is used too often and within a certain period of time. I removed the "frequency" parameter below in order to relieve the database, however the problem remains the same.

<%= observe_form 'searchform',    
#:frequency => 0.5, #too heavy for the database
:update => 'carSearch',
:before => "Element.show('spinner')",
:success => "Element.hide('spinner')",
:url => {:action => 'cars'} %>

What must I do to release the pressure from the database?
The website is still here by the way.

Last edited by Pixelateur (2006-12-14 20:07:56)

Re: Ajax Live Search with MULTIPLE search criteria

Check the logs, how frequently is a request being sent? Also, how long does the search usually take? You may want to do some stress testing to find the bottlenecks. Unfortunately I'm not sure of the best way to do that as I have little experience in that area. I need to learn it sooner or later though.

Railscasts - Free Ruby on Rails Screencasts

Re: Ajax Live Search with MULTIPLE search criteria

Hi Ryan,

You are right, there are far too many server requests (depending on how fast the user can click on the select boxes). So I decided to abandon my initial AJAX approach in favour of a good old form submit button. That will make the amount of server requests more manageable.

I changed my controller to:

  def cars
    make      = params[:make]
    style     = params[:style]
    fuel      = params[:fuel]
    min_price = params[:min_price]
    max_price = params[:max_price]
    conditions = ["make LIKE ? and style LIKE ? and fuel LIKE ? and price >= ? and price <= ?", make, style, fuel, min_price, max_price]
    @vehicle_pages, @vehicles = paginate(:vehicles, :conditions => conditions, :per_page => 5)
    @total = @vehicle_pages.item_count
  end

(please let me know if there's a more elegant way to write this...)

My view looks like this:

<%= start_form_tag :action => 'cars'  %>
   
    <h1>Car Search</h1>

    <p>by Car
        <%= select('make',     params[:make],     [[ "All Makes", '%' ]] + Vehicle::MAKE) %>
        <%= select('style', params[:style], [[ "All Body Styles", '%' ]] + Vehicle::STYLE) %>
        <%= select('fuel',     params[:fuel],     [[ "All Fuel Types", '%' ]]+ Vehicle::FUEL) %>
    </p>

    <p>by Budget
        <%= htmlarize(select_tag 'min_price', price_options(Vehicle::MINPRICE, 'over ', 'No Min Price', 0)) %>
        <%= htmlarize(select_tag 'max_price', price_options(Vehicle::MAXPRICE, 'under ', 'No Max Price', 999999)) %>
    </p>
   
    <%= submit_tag "Go!" %>
    <%= link_to 'Reset form', :action => 'cars' %>
   
<%= end_form_tag %>


That's pretty much all there is to it! But again... I'm stuck.

Running the form once, works fine, but then making adjustments to the initial input breaks the SQL generated by Rails. It makes the SQL look like this:

[0mSELECT * FROM vehicles WHERE (make LIKE '--- \n- Mercedes%\n- \"%\"' and style LIKE '--- \n- \"%Convertible\"\n- Coupe' and fuel LIKE '--- \n- \"%%\"\n- \"%\"' and price >= '0' and price <= '999999') LIMIT 0, 5

I've never seen that before. Is Rails inserting line breaks into the SQL?? This only happens when running the form a second time (the first time it usually works).

By the way, the current version is online.

Last edited by Pixelateur (2006-12-15 13:18:52)

Re: Ajax Live Search with MULTIPLE search criteria

Pixelateur wrote:

        <%= select('make',     params[:make],     [[ "All Makes", '%' ]] + Vehicle::MAKE) %>

The "select" method is designed to edit a model's attribute. The first parameter is the name of the model, the second is the name of the attribute. But in this case you are passing the parameter as the attribute, so it's messing it up. Instead of using "select", "select_tag" is probably what you want.

<%= select_tag('make', options_for_select([[ "All Makes", '%' ]] + Vehicle::MAKE, params[:make])) %>

Railscasts - Free Ruby on Rails Screencasts

Re: Ajax Live Search with MULTIPLE search criteria

Hi Ryan,
You solved almost all my troubles in one go. It works, except for the Min_Price and Max_Price boxes, which keep jumping back to their default state after being used. It must be due to this little helper method you suggested a few weeks back (thanks again by the way):

  def price_options(prices, prefix = '', default = '', value = 0)
    options = prices.collect { |p| [prefix + p, p] }
    options[0] = [default, value]
    options_for_select(options)
  end

It is being used in the form:
<p>by Budget
<%= htmlarize(select_tag 'min_price', price_options(Vehicle::MINPRICE, 'over ', 'No Min Price', 0)) %>
<%= htmlarize(select_tag 'max_price', price_options(Vehicle::MAXPRICE, 'under ', 'No Max Price', 999999)) %>
</p>

Last edited by Pixelateur (2006-12-15 15:19:57)

Re: Ajax Live Search with MULTIPLE search criteria

You'll need to pass the selected value (params[:min_price]) to this price_options method. You can do it like this:

# view
price_options(Vehicle::MINPRICE, 'over ', 'No Min Price', 0, params[:min_price])

# helper
  def price_options(prices, prefix = '', default = '', value = 0, selected = nil)
    options = prices.collect { |p| [prefix + p, p] }
    options[0] = [default, value]
    options_for_select(options, selected)
  end


The method parameters are getting a little long. There's probably a better way to do it, but best to get it working first.

Railscasts - Free Ruby on Rails Screencasts