CODEWITHSUNDEEP
mysqlone-to-many
Martin
Martin

Reputation: 125

MySql filter query

I'm trying to create a product filters feature and I'm having trouble trying to get the right query together based on what a user has selected. lets say the tables are:

product_tb
+-----------+
|product_sku|
+-----------+
|ABC        |
|XYZ        |
|-----------|

product_attribute_tb

+-----------+--------------+---------------+
|product_sku|attribute_name|attribute_value|
+-----------+--------------+---------------|
|ABC        |colour        |red            |
|ABC        |size          |M              |
|XYZ        |colour        |red            |
|XYZ        |size          |L              |
|-----------|--------------|---------------|

What I'm looking for is that a product is only returned if all attributes exist, so if a user selects 'red' and 'M', it would return ABC only.

The reason why I haven't put these attributes on the product table is because its a SaaS application and the filters will differ for different applications.

I can't do multiple joins with hardcoded names and also there could be 40 potential filters against products do I'm looking for this to be flexible based on the name and content values of the product_attribute_tb as these can change.

Maybe I'm missing something obvious but if anyone could help it would be appreciated.

Thanks, Martin.

Upvotes: 0

Views: 43

Answers (1)

fancyPants
fancyPants

Reputation: 51938

SELECT 
pat.product_sku
FROM
product_attribute_tb pat
INNER JOIN product_tb pt ON pat.product_sku = pt.product_sku
GROUP BY pat.product_sku
HAVING SUM((attribute_name = 'colour' AND attribute_value = 'red') OR (attribute_name = 'size' AND attribute_value = 'L')) >= 2;

Each boolean expression like (attribute_name = 'colour' AND attribute_value = 'red') and (attribute_name = 'size' AND attribute_value = 'L') returns true or false, 1 or 0. Then we sum those for each row and check if the true values are more than 2. Feel free to join to your product_tb table.

Upvotes: 1

Related Questions