CODEWITHSUNDEEP
mysqlruby-on-rails
Victor
Victor

Reputation: 13368

Search wildcard multiple columns in Rails using one param

Using Rails 3.2. I have the following table:

name                 address
=======================================
Hilton Hotel         New York, USA
Hilton Hotel         Paris, France
Mandarin Hotel       Chicago, USA
Le Meridien Hotel    New York, USA

and the following query:

term = "%#{params[:term]}%"
shops = Shop.limit(10).where("name LIKE ? OR address like ?", term, term)

My expected result is this:

Search - "Hilton"
Result - "Hilton Hotel, New York, USA"; "Hilton Hotel, Paris, France"

Search - "Hilton USA"
Result - "Hilton Hotel, New York, USA"

Search - "New York"
Result - "Hilton Hotel, New York, USA"; "Le Meridien Hotel, New York, USA"

How should I rewrite my query?

Thanks.

Upvotes: 1

Views: 1282

Answers (2)

Victor
Victor

Reputation: 13368

I use another workaround for this.

I create another column for_autocomplete and concatenate the name and address into it. Every time a record is created/updated, it will create/update for_autocomplete accordingly. Then I just search through the for_autocomplete column.

Upvotes: 0

Benjamin M
Benjamin M

Reputation: 24527

You could use MySQL's MATCH AGAINST. There's no direct support in Rails, but you can always roll your own custom query in Rails using

YourModel.find_by_sql("SELECT ... WHERE MATCH(...) AGAINST(...)")

or a bit more Rails style (have not tested this):

YourModel.where("MATCH(...) AGAINST(...)")

For more information, have a look here: http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html

EDIT: I'd split the input string by space, comma, dot, etc. and then use MATCH AGAINST to get all results. So if you have this table:

      col1 | col2
      -----------
row1: a c  | a
row2: a d  | b e
row3: b e  | a d
row4: b    | b c

And the user types a as input. You should do

MATCH(col1, col2) AGAINST ('a')

This will return:

row1: 'a c', 'a'
row2: 'a d', 'b e'
row3: 'b e', 'a d'

Upvotes: 1

Related Questions