MySQL is the best way to build this query?
I inherited a database that has a structure with a product table, a table made up of some product attributes and another table to build a relationship between those attributes and a given product.
The user can filter products by a combination of these attributes, which means that if more than one attribute is selected, only products with all of these attributes are returned. Unfortunately, there is now an exception to this rule whereby a user choosing one of two specific attributes needs results that contain either (or both).
The request currently looks like this (not my code):
SELECT DISTINCT p.* FROM products AS p
INNER JOIN attributes a ON p.product_id=a.property_id
WHERE a.attribute_id IN (1,3,7)
GROUP BY p.property_id
HAVING COUNT(DISTINCT a.attribute_id) = 3
I doubt this is a particularly effective way to get the products you need, but I'm not sure how to proceed in light of the new requirement.
Now I have created some php code to make a custom request when two "special" attributes (3 and 7) are selected:
SELECT DISTINCT p.* FROM products AS p
INNER JOIN attributes a ON p.product_id=a.property_id
WHERE a.attribute_id IN (1,3) OR a.attribute_id IN (1,7)
GROUP BY p.property_id
HAVING COUNT(DISTINCT a.attribute_id) = 2
However, this still doesn't work as needed - any products that share both of these attributes are not returned in the result (this is obviously related to the HAVING COUNT clause, but I don't know how I fix it. For clarity, the problem is Since 10 products only have attribute 3, but five more have attributes 3 and 7, the above query will only return 10 records.
Is it possible to use any subquery or what are the alternatives?
source to share
The query seems fine, except that you can remove the DISTINCT modifier since you are already grouping by id. Regarding the new requirement, can you solve it in your code before it reaches the SQL query?
Edit: An alternative would be to create a query with one inner join for each required attribute, but that would probably be much slower
source to share
This is the best way to make the original request:
SELECT ... FROM products AS p
INNER JOIN attributes a1 ON p.product_id=a1.property_id AND a1.attribute_id=1
INNER JOIN attributes a2 ON p.product_id=a2.property_id AND a2.attribute_id=3
INNER JOIN attributes a3 ON p.product_id=a3.property_id AND a3.attribute_id=7
And, given that if you want id_attribute 3, you want an OR with id_attribute 7, and assuming you also want id_attribute 1, which is not one of these special attributes:
SELECT ... FROM products AS p
INNER JOIN attributes a1 ON p.product_id=a1.property_id AND a1.attribute_id=1
LEFT OUTER JOIN attributes a2 ON p.product_id=a2.property_id AND a2.attribute_id=3
LEFT OUTER JOIN attributes a3 ON p.product_id=a3.property_id AND a3.attribute_id=7
WHERE a2.attribute_id IS NOT NULL OR a3.attribute_id IS NOT NULL
I suspect any of these will be much faster than the originals with a separate / having / group on aggregate operations. The attribute table should have a unique multi-column index (property_id, attribute_id) or (attribute_id, property_id), although I assume property_id is more selective and therefore should be the leftmost column in the index.
source to share
With appropriate (and obvious) indexes, this will be very efficient in MySQL.
SELECT ...
FROM products AS p
INNER JOIN attributes a1 ON p.product_id = a1.property_id AND a1.attribute_id = 1
LEFT JOIN a2 attributes ON p.product_id = a2.property_id AND a2.attribute_id = 3
LEFT JOIN a3 attributes ON p.product_id = a3.property .attribute_id = 7
WHERE (
CASE WHEN a1.product_attribute_id NULL THEN 0 ELSE 1 END
+ CASE WHEN a1.product_attribute_id NULL THEN 0 ELSE 1 END
)> 0
source to share
how can you choose p. * and a group of only 1 column? Or does it work with primary key?
WHERE a1.attribute_id IN (1,3) OR a1.attribute_id IN (1,7)
coincides with
WHERE a1.attribute_id IN (1,3,7)
SELECT p.* FROM products
INNER JOIN (
SELECT a1.property_id
FROM attributes a1
WHERE a1.attribute_id IN (1,3,7)
GROUP BY a1.property_id
HAVING COUNT(DISTINCT a1.attribute_id) = 2
) as a ON p.product_id=a.property_id
source to share