Michael Cropper Michael Cropper - 4 months ago 9
MySQL Question

MySQL Full Text Search Match Against Query AND OR Column1 = ABC

This works;

SELECT MATCH('Col1', 'Col2') AGAINST('search query') AS Score, Col1, Col2, Col3
FROM my_table
WHERE
Col1 = 'something-1'
OR
Col2 = 'something-2'
OR
MATCH('Col1', 'Col2') AGAINST ('search query') HAVING Score > 2;


This doesn't;

SELECT MATCH('Col1', 'Col2') AGAINST('search query') AS Score, Col1, Col2, Col3
FROM my_table
WHERE
Col1 = 'something-1'
OR
Col2 = 'something-2'
OR
MATCH('Col1', 'Col2') AGAINST ('search query') HAVING Score > 2
AND Col3 = 'something3';


I'm not sure this is bringing back the exact results I want yet, although this is the main issue: How to use Match Against with an AND, and most importantly, chain things together effectively with (brackets) to prioritise the correct mixture of;

(X = 1 OR Y = 2 OR MATCH('Col1', 'Col2') AGAINST ('search query') HAVING Score > 2) AND Z = 3


From my tests for every combination so far, I've not managed to get this to work effectively. I'm trying to expand a current complex query which just uses AND and ORs currently to also include MATCH AGAINST information which is of a certain quality.

There must be a correct way of doing this that I'm not doing properly.

Update 1
Interestingly, when I implement the query suggested and use the AND statement first as below;

Col3 = 'something3' AND
(
Col1 = 'something-1'
OR
Col2 = 'something-2'
OR
MATCH('Col1', 'Col2') AGAINST ('search query') > 2
);


Then this actually returns duplicate results.

Answer

seems that you simply need

(
    Col1 = 'something-1' 
    OR 
    Col2 = 'something-2' 
    OR 
    MATCH('Col1', 'Col2') AGAINST ('search query')  > 2 
)
AND Col3 = 'something3';