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
– WonderLand, Commented May 7, 2015 at 4:45
Check the MATCH function. It has a "scoring" feature that should work for you — cha
– cha, Commented May 7, 2015 at 5:10
unfortunately fullindex is not available and so the match function — WonderLand
– WonderLand, Commented May 7, 2015 at 5:16

LoztInSpace · Accepted Answer · 2015-05-07 07:10:16Z

1

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.

answered May 7, 2015 at 7:10

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

WonderLand Over a year ago

+1 for the effort :) I think I found a better approach using CASE in the ORDER BY part like described here stackoverflow.com/questions/8533438/…

Community · Accepted Answer · 2017-05-23 12:21:54Z

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

Collectives™ on Stack Overflow

Sql select record that match multiple words for a give column

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related