I'm working on a simple search function with SQL and got stuck. I use GROUP_CONCAT to put all keywords in a single row. The keywords are taken from categories.
Not all columns are in this example. I've stripped it down
SELECT
p.id as id,
p.title as title,
p.title as company,
GROUP_CONCAT(DISTINCT cat.title SEPARATOR " ") as keywords,
FROM products p
JOIN product_categories pc ON p.id = pc.product_id
JOIN categories cat ON pc.category_id = cat.id
JOIN companies co ON p.company_id = co.id
WHERE MATCH (p.title) AGAINST ("Test")
OR MATCH (co.title) AGAINST ("Test")
GROUP BY p.id
keywords contains something like keyword1 keyword2 keyword3.
The SQL above is working. However, I can't use MATCH AGAINST with a virtual value keywords value.
I've read about what I think is an ugly workaround, to add this:
HAVING keywords LIKE "%keyword1%"
LIKE is probably slow and because HAVING is after GROUP BY I can't use OR so HAVING in this case will override the WHERE.
Ideally, I would like to use a MATCH AGAINST on keywords as well. So, what are my options?
I probably need an example code to understand.
This doesn't answer the question you asked, but I think adding this condition to the WHERE clause of the query:
OR EXISTS ( SELECT 1
FROM categories k
WHERE k.id = pc.category_id
AND k.title IN ('Test1','Test2')
)
before the GROUP BY would eliminate the need to scan the return from GROUP_CONCAT, if the specification is to return rows where the category title matches one of the specified search words.
If there's not a requirement to actually return the derived keywords column in the resultset, I'd avoid the GROUP BY.
SELECT p.id AS id
, p.title AS title
, p.title AS company
FROM products p
JOIN companies co
ON co.id = p.company_id
WHERE MATCH (p.title) AGAINST ('Test')
OR MATCH (co.title) AGAINST ('Test')
OR EXISTS ( SELECT 1
FROM categories k
JOIN product_categories pc
ON pc.category_id = k.id
WHERE pc.product_id = p.id
AND k.title IN ('Test1','Test2')
)
ORDER BY p.id