Search wildcard multiple columns in Rails using one param

Question

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.

You can't match single string which contains substring of two attributes unless you have some separator. In your search query atleast you should take comma separated values. Like `Hilton, USA`. SO you can split on `,`. If its single string like `New York` then you give to match both attribute with same string. — Rahul Tapali, Feb 17 '13 at 16:41
It may not be the best way, but you can split the string on `,` and space, then match the two columns with each split parts. — Manoj Monga, Feb 17 '13 at 18:06

Benjamin M · Answer 1 · 2013-02-17T17:02:16.877

1

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'

edited Feb 17 '13 at 17:02

answered Feb 17 '13 at 16:45

Benjamin M

23,599
32
121
201

This only can be used on MyISAM table. I am using InnoDB. :( – Victor Feb 17 '13 at 17:19
Maybe you could use this: http://stackoverflow.com/questions/2638698/mysql-full-text-search-workaround-for-innodb-tables – Benjamin M Feb 17 '13 at 21:05
Since MySQL 5.6, this is supported on InnoDB as well. Tested and it works great. – Joshua Pinter Dec 29 '19 at 20:09

score 0 · Accepted Answer · answered Feb 19 '13 at 04:01

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.

Search wildcard multiple columns in Rails using one param

2 Answers2