Return records with common words between them in mysql and rails 3

please help with this mysql query in rails 3:

In my controller i have:

@projects = Project.where("name LIKE ?", "%#{params[:search]}%")

If in my Project table under the "name" column i have the following records:

1). water

2). aquaduct water bridge

3). bridge waterway

If params[:search] = "water", then records 1, 2, 3 are returned. However when params[:search] = "aquaduct water bridge", then record 2 is only returned.

What i need is to search each word(if there are many words in params[:search] string) and return those records found. So for example if params[:search] = "aquaduct water bridge", it must return all records because water is common in al开发者_StackOverflow中文版l 3 records.

I am just using water as an example. must apply to any word that is common between records.

Thank you for your help, much appreciated.

One easy way to do this to split the params[:search] and make a query like this:

WHERE name LIKE "%aquaduct%" OR name LIKE "%water%" OR  name LIKE "%bridge%"

But this is inefficient method.

Try to use MATCH() AND AGAINST() method of mysql for full text searching. Have a look on the link.

http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

Updated:

ALTER TABLE projects ADD FULLTEXT(name);

You could split up your target string, and construct a where clause. In the example you give, it would be:

WHERE name LIKE "%aquaduct%" OR name LIKE "%water%" OR  name LIKE "%bridge%"

Not pretty, but it works.

I will prefer @Ashish's way.. and suggest, if you could use 'scope', your query will be more eligent

cheers

sameera