php – symmetric difference or disjunctive union of mysql queries-ThrowExceptions

Exception or error:

If a customer searches for two or more keywords, I need to return two sets of MySQL results:

1) Primary results: The intersect of those keywords (items only matching all keywords)

2) Secondary results: Items matching the symmetric difference/disjunctive union of the keywords (items matching some but not all keywords)

I can perform query #1 using BOOLEAN mode with a + sign before each keyword. I can perform a query that includes both #1 and #2 by leaving the + sign off, but I don’t know how to get the desired results described in #2. Any suggestions?

How to solve:

For #2 You just need a combination of:

WHERE MATCH(words) AGAINST('keyword1 keyword2 ... keywordN' IN BOOLEAN MODE)
AND NOT MATCH(words) AGAINST('+keyword1 +keyword2 ... +keywordN IN BOOLEAN MODE)

This will return rows that have at least one keyword but not all. Or have I misunderstood your question?

Schema (MySQL v8.0)

create table test (
  id int not null auto_increment primary key,
  words text not null,
  FULLTEXT idx (words)
  );

  insert into test(words)
     values('Arc Book Cow Dog'), ('Book Cow Fox'), ('Arc Book Cow Fox'), ('Book Cow');

Query #1

SELECT *
FROM test
WHERE MATCH(words) AGAINST('Arc Fox' IN BOOLEAN MODE)
      AND NOT MATCH(words) AGAINST('+ARC +Fox' IN BOOLEAN MODE)
ORDER BY id;

| id  | words            |
| --- | ---------------- |
| 1   | Arc Book Cow Dog |
| 2   | Book Cow Fox     |

View on DB Fiddle

Leave a Reply

Your email address will not be published. Required fields are marked *