I want to be able to search for my products (search in either the product name or product "codes"), and then filter in those results as well.
I don't know what to search for in Google or SO, every time I get Search GROUP_CONCAT using LIKE or Php and MySQL GROUP_CONCAT with separator comma and search where concat by comma come up, but I can't seem to wrap my head around HAVING without getting an error in my database. I'm not sure if this is even possible or if I'm just way off.
My tables are like so:
Products Table
+------------+--------------+----------------+-------------------------+---------------+
| product_id | product_name | product_status | product_date_available | product_type |
+------------+--------------+----------------+-------------------------+---------------+
| 1 | Rug | 1 | 2020-01-24 | 0 |
| 2 | Scraper | 1 | 2020-03-15 | 0 |
| 3 | Shovel | 0 | 2019-04-24 | 0 |
| 4 | Bucket | 1 | 2030-01-01 | 0 |
| 5 | Dog | 1 | 2018-04-27 | 0 |
| 6 | Digging | 1 | 2020-01-01 | 1 |
+------------+--------------+----------------+-------------------------+---------------+
Products Category table
+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1 | 17 |
| 2 | 22 |
| 3 | 22 |
| 4 | 17 |
+------------+-------------+
Products Tag Table
+------------+--------+
| product_id | tag_id |
+------------+--------+
| 1 | 11 |
| 2 | 6 |
| 3 | 7 |
| 4 | 7 |
+------------+--------+
Products Codes Table
+---------+------------+------------+
| code_id | product_id | code_value |
+---------+------------+------------+
| 1 | 1 | 47012.000 |
| 2 | 1 | 47012.001 |
| 3 | 2 | 47013.000 |
| 4 | 3 | 47014.000 |
| 5 | 3 | 47014.001 |
| 6 | 3 | 47014.002 |
+---------+------------+------------+
Product Variants Values Table
+-------+------------+----------+
| pv_id | product_id | value_id |
+-------+------------+----------+
| 1 | 1 | 7 |
| 2 | 1 | 6 |
| 3 | 1 | 10 |
| 4 | 2 | 7 |
| 5 | 3 | 10 |
| 6 | 3 | 7 |
| 7 | 3 | 12 |
| 8 | 2 | 14 |
+-------+------------+----------+
This is what I've tried but can't get to work:
Search for product code
SELECT p.product_id,
(SELECT GROUP_CONCAT(DISTINCT code_value) FROM `me_product_variant_codes` WHERE `product_id` = p.product_id) AS codes,
(SELECT GROUP_CONCAT(DISTINCT value_id) FROM `me_product_variant_values` WHERE `product_id` = p.product_id) AS variant_values,
(SELECT GROUP_CONCAT(DISTINCT tag_id) FROM `me_product_tags` WHERE `product_id` = p.product_id) AS product_tags
FROM me_product AS p
WHERE (p.product_status = '1' AND p.product_date_available <= CONVERT_TZ(NOW(), 'SYSTEM', '+13:00') AND p.product_type = '0')
AND ((LOWER(p.product_name) LIKE '%47012%')
GROUP BY p.product_id
HAVING codes LIKE '%47012%'
AND HAVING variant_values IN(7, 10)
AND HAVING product_tags IN(11)
ORDER BY product_name DESC
LIMIT 24
I'm well aware that I probably have this very wrong, but after weeks of research, I don't seem to be any closer to finding an answer on how to make this work.
So I expect as an example if I search for 47012 it will return the following based on the example data above.
+------------+----------------------+----------------+--------------+
| product_id | codes | variant_values | product_tags |
+------------+----------------------+----------------+--------------+
| 1 | 47012.000, 47012.001 | 7, 6 | 11 |
+------------+----------------------+----------------+--------------+
But I'm currently getting this error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'GROUP BY p.product_id
HAVING codes LIKE '%47012%'
AND HAVING variant_v' at line 8
Even trying a simple version of my query to at least get some result gives me an error
Simplified query
SELECT p.product_id,
(SELECT GROUP_CONCAT(DISTINCT code_value) FROM `me_product_variant_codes` WHERE `code_product_id` = p.product_id) AS codes
FROM me_product AS p
WHERE (p.product_status = '1' AND p.product_date_available <= CONVERT_TZ(NOW(), 'SYSTEM', '+13:00') AND p.product_type = '0')
AND ((LOWER(p.product_name) LIKE '%47012%')
GROUP BY p.product_id
HAVING codes LIKE '%47012%'
ORDER BY p.product_name DESC
LIMIT 24
Gives me this error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'GROUP BY p.product_id
HAVING codes LIKE '%47012%'
ORDER BY p.product_n' at line 6
The other issue I've just noticed is that I'm not sure how this will return results if it matches the product_name OR one of the codes values
You should get rid of all those correlated subqueries and instead use joins to subqueries which compute the various CSV terms:
SELECT
p.product_id,
COALESCE(vc.codes, 'NA') AS codes,
COALESCE(vv.variant_values, 'NA') AS variant_values,
COALESCE(pt.product_tags, 'NA') AS product_tags
FROM me_product AS p
LEFT JOIN
(
SELECT product_id, GROUP_CONCAT(DISTINCT code_value) AS codes
FROM me_product_variant_codes
WHERE code_value LIKE '%47012%'
GROUP BY product_id
) vc
ON vc.product_id = p.product_id
LEFT JOIN
(
SELECT product_id, GROUP_CONCAT(DISTINCT value_id) AS variant_values
FROM me_product_variant_values
GROUP BY product_id
HAVING COUNT(CASE WHEN value_id = 7 THEN 1 END) > 0 AND
COUNT(CASE WHEN value_id = 10 THEN 1 END) > 0
) vv
ON vv.product_id = p.product_id
LEFT JOIN
(
SELECT product_id, GROUP_CONCAT(DISTINCT tag_id) AS product_tags
FROM me_product_tags
GROUP BY product_id
HAVING COUNT(CASE WHEN tag_id = 11 THEN 1 END) > 0
) pt
ON pt.product_id = p.product_id
WHERE
p.product_status = '1' AND
p.product_date_available <= CONVERT_TZ(NOW(), 'SYSTEM', '+13:00') AND
p.product_type = '0' AND
LOWER(p.product_name) LIKE '%47012%';