1

I have the following in my controller:

def autocomplete
  spots = Spot.where("name LIKE ?", "%#{params[:term]}%")
  render :json => spots.map(&:name)
end

Here is in my view:

<%= label_tag :term, "Term" %> <%= search_field_tag :term %>
...
$("input#term").autocomplete({
  source: '<%= autocomplete_places_path %>',
  minLength: 2,
  delay: 500
});

Let's says I have this set of records:

| id |   name   |     address     |
|  1 |  Stall A |   Fruity Road   |
|  2 |  Stall B |  Chinky Street  |
|  3 | Restroom |  White Garden   |

When I search stall, record 1 and 2 will show.

What I want to achieve is, when I search stall fru, only record 1 will show. At the moment, no record is shown if I search this term.

I know I have to rewrite the query, but how do I go about it?

Many thanks.

Victor
  • 13,010
  • 18
  • 83
  • 146
  • How advanced to you want the query to be? Will the second part of the query always relate to the address column, or should the words in the query exist in at least one of the columns? – erikxiv May 02 '12 at 14:56
  • 1
    Being able to search by multiple columns is a different concern then implementing autocomplete. You will have a lot of issues validating user input if you want to have a single string parsed into a format that can be searched and returned as an autocomplete string. For instance, what if someone just types "fruity" and not "stall" first? How can you tell from someone's partial string which columns they are wanting to search, and in what order? So, you probably need to think about how you can extract the information you want from the user first, then write the methods to achieve it. – Andrew May 02 '12 at 15:01
  • @Andrew If `fruity` is typed, record `1` should be shown. Do you think having a column that combines `name` and `address` to `searchable` column, then let sql search that column instead, will be a good idea? – Victor May 02 '12 at 15:05
  • Maybe, it just depends on how much you want to combine and how complex your searching needs to be. For just two columns that would probably be viable. – Andrew May 02 '12 at 15:07

1 Answers1

0

What you are trying to implement in your new solution is called a "full text search".

There are many gems that can help you to achieve this, but they are beyond the scope of this answer right now. This S/O question links to bunches of gems that do the job: Full Text Searching with Rails

Alternatively, I recommend googling "rails gems full text search" - you'll get a lot of different results.

The "squeeze the two columns together" approach mentioned in the comments is quick and easy, and will work for the two examples you got above eg "stall fru" and "fruity", but would probably not find anything if you tried "fruity stall". This may or may not be fine with you.

Depending on which db you use, there might also be a native way of doing it on your db. To find out more, the google search to use would be " full text search"

Community
  • 1
  • 1
Taryn East
  • 27,486
  • 9
  • 86
  • 108