Select products by multiple attributes, using AND instead OR concatenator, Data model EAV

Question

I have an issue with a query for eCommerce website products filter.

I have EAV data model like this:

products          [id, title....]
attributes        [id, name]
attributes_entity [product_id, attribute_id, value_id]
attributes_values [id, value]

My query:

SELECT 
products.id, 
products.title 
FROM 
products 
WHERE products.id IN 
(
SELECT attributes_entity.product_id FROM attributes_entity INNER JOIN 
attributes ON attributes_entity.attribute_id=attributes.id INNER JOIN 
attributes_values ON attributes_entity.value_id=attributes_values.id 
WHERE 
(
(attributes.name="Memory" AND attributes_values.value="16GB") 
>> AND
(attributes.name="Color" AND attributes_values.value="Gold")
)
) 
AND products.category_id = :category_id

The problem is when I'm using >> AND between (attributes.name="Memory" AND attributes_values.value="16GB") subqueries I get 0 results but when I'm using OR it gives me more results that i need which not relevant for this 2 conditions.

For example when I'm get request: "Memory = 16GB and Color = Black". I'm expect to get iPhone 5 16BG Black, not all (gold, space gray etc.) iPhones with 16GB

I'm looking for query example, preferably with description of how it works.

Regards, Anton

Answer 1

Your subquery should be like this:

SELECT
  attributes_entity.product_id
FROM
  attributes_entity INNER JOIN attributes
  ON attributes_entity.attribute_id=attributes.id
  INNER JOIN attributes_values ON
  attributes_entity.value_id=attributes_values.id 
WHERE 
  (attributes.name="Memory" AND attributes_values.value="16GB") 
  OR
  (attributes.name="Color" AND attributes_values.value="Gold")
GROUP BY
  attributes_entity.product_id
HAVING
  COUNT(DISTINCT attributes.name)=2

this solution uses a GROUP BY subquery. You have to use OR because the attribute can't be Memory and Color at the same time on the same row, they can both be true but on different rows. COUNT(DISTINCT attributes.name) counts the number attributes that either Color or Memory, if it's 2 then there is at least 1 row where the first condition is true and 1 row where the other is also true.