Group concat with where match against in mysql

Question

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?

• Some kind of virtual table?
• Subquery?
• Something else?

I probably need an example code to understand.

Answer 1

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