Sql select record that match multiple words for a give column

Question

Suppose I have a table that list some items with a column title

Now given an item with title "AAA BBB CCC DDD" I would like to retrieve all items that has at least n-1 matching words.

At the moment I'm producing all combination (php side) of n-1 words and then use a LIKE to do the match, so something like this:

SELECT
  `e`.`title`
FROM `items` AS `e`
WHERE ((
  (e.title LIKE '%AAA%' AND e.title LIKE '%BBB%' AND e.title LIKE '%CCC%') OR
  (e.title LIKE '%AAA%' AND e.title LIKE '%BBB%' AND e.title LIKE '%DDD%') OR
  (e.title LIKE '%AAA%' AND e.title LIKE '%CCC%' AND e.title LIKE '%DDD%') OR
  (e.title LIKE '%BBB%' AND e.title LIKE '%CCC%' AND e.title LIKE '%DDD%')))

Questions

I'm far from being an SQL guru :) Is there a better approach that the above example ?
An extra requirement would be to search for items with n-2 words in case no item with n-1 is found. My idea is to find all matching items with n-1, n-2 until 2 and in the result set have a dynamic column containing the number of matched words and so order the result on the base of this score, is it possible ?

extra info:

I cannot alter the table structure/property. ( I will break the system "consistency" as I'm on Magento so for "Item" I mean products)
Title is actually on a separate table so in the real scenario it is on a joined table

consider the example query is simplified in the real scenario I have the title on a second table so I have to do a join ... said that I'm not sure what it is full text index but I'm sure I cannot alter the table index — WonderLand, May 07 '15 at 04:45
Check the [MATCH](https://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html) function. It has a "scoring" feature that should work for you — cha, May 07 '15 at 05:10
unfortunately fullindex is not available and so the match function — WonderLand, May 07 '15 at 05:16

score 1 · Answer 1 · answered May 07 '15 at 07:10

it won't be quick but something like this:

SELECT
  `e`.`title`, count(*)
FROM `items` AS `e`
inner join 
(select 'AAA' as search union all select 'BBB' union all select 'CCC') as z
on `e`.`title` like concat('%',`z`.`search`,'%')
group by `e`.`title`
order by COUNT(*) desc

I'm no MySQL expert so you might have to muck around with that a little bit. Also if the titles are not unique then select & group by the primary key as well as the title.

If your search parameters are already in a table then that's good - you don't have to select them again.

I don't suppose this has completely solved your problem but I hope it helps you get there.

+1 for the effort :) I think I found a better approach using CASE in the ORDER BY part like described here http://stackoverflow.com/questions/8533438/count-and-order-by-where-clause-matches — WonderLand, May 07 '15 at 09:21

score 0 · Answer 2 · edited May 23 '17 at 12:21

I have used the approach discussed here: Count and Order By Where clause Matches

Here an example give an item with title AAA BBB CCC

Note
For words permutation (as we don't want reinvent the wheel right ? )
I have used this library http://pyrus.sourceforge.net/Math_Combinatorics.html

I'm not posting the full php code since it is not directly related to the question ( also it is a based on Zend and it will only create more confusion )

SELECT item_id
FROM `item` AS `title_match`
WHERE
  (
    (title_match.value LIKE '%AAA%' AND title_match.value LIKE '%BBB%') OR
    (title_match.value LIKE '%AAA%' AND title_match.value LIKE '%CCC%') OR
    (title_match.value LIKE '%BBB%' AND title_match.value LIKE '%CCC%')
  )
ORDER BY CASE WHEN `title_match`.`value` LIKE '%AAA%' THEN 1
         ELSE 0 END +
         CASE WHEN `title_match`.`value` LIKE '%BBB%' THEN 1
         ELSE 0 END +
         CASE WHEN `title_match`.`value` LIKE '%CCC%' THEN 1
         ELSE 0 END
DESC

Sql select record that match multiple words for a give column

2 Answers2