Must match all words in MATCH AGAINST mysql

Question

SELECT *,
MATCH (`text`) AGAINST ('my work involves for' IN BOOLEAN MODE) `score`
FROM `messages` WHERE MATCH (`must_match`) AGAINST ('my work involves for' IN BOOLEAN MODE)
ORDER BY `score` DESC

So here user input is 'my work involves for'

id   text                 must_match
---  ----                 ------
1    my work is to help   work,help
2    work involves help   involves,work

Now I want result if mus_match column's all words match in user input So, from above row our result will be 2nd row

must_match column will contain words from text column

Does `must_match` always have 2 comma separated values? – Harshil Doshi Nov 20 '17 at 10:24 — Harshil Doshi, Nov 20 '17 at 10:24
No, that can be more than 2 – Himanshu Upadhyay Nov 20 '17 at 10:34 — Himanshu Upadhyay, Nov 20 '17 at 10:34

score 0 · Answer 1 · answered Nov 20 '17 at 12:20

After spent a lot of time, I am unable to come up with a solution/workaround for your task. The main reason behind this is that you have Multi-valued Attribute in your table: must_match. It contains comma-separated values and number of values are also not predefined.

First of all, I will request you to go through this link in order to realize the cons of storing a delimited list in a column.

id        text1         must_match
1   my work is to help  work
1   my work is to help  help
2   work involves help  involves
2   work involves help  work

Now, if you are able to convert your table in 1NF and store multiple values of must_match in multiple rows (as described above) then keep reading for solution and if not, please comment the reason behind that.

Solution:

select id,text1,
MATCH (text1) AGAINST ('my work involves for' IN BOOLEAN MODE) as score
from t t1
where locate(must_match,"my work involves for") > 0
group by id
having count(*) = (select count(*) from t where id = t1.id);

Note: If id is a Primary key,then you can group by using text column too.

Click here for Demo

Hope it helps!

Is it an affordable approach for you? – Harshil Doshi Nov 25 '17 at 00:00 — Harshil Doshi, Nov 25 '17 at 00:00

Must match all words in MATCH AGAINST mysql

1 Answers1