CODEWITHSUNDEEP
mysqlmatchgroup-concatagainst
Jens Törnell
Jens Törnell

Reputation: 24768

Group concat with where match against in mysql

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.

Upvotes: 1

Views: 1143

Answers (1)

spencer7593
spencer7593

Reputation: 108400

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

Upvotes: 1

Related Questions