I have an issue with a query for eCommerce website products filter.
我对电子商务网站产品过滤器的查询存在问题。
I have EAV data model like this:
我有这样的EAV数据模型：
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.
问题是当我在（attributes.name =“Memory”和attributes_values.value =“16GB”）子查询之间使用>> AND时，我得到0结果但是当我使用OR时它给了我更多我需要的结果与这两个条件无关。
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
例如，当我收到请求时：“内存= 16GB，颜色=黑色”。我期望得到iPhone 5 16BG Black，而不是所有（金色，太空灰等）iPhone 16GB
I'm looking for query example, preferably with description of how it works.
我正在寻找查询示例，最好描述它是如何工作的。
Regards, Anton
此致，安东
1 个解决方案
#1
2
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.
此解决方案使用GROUP BY子查询。您必须使用OR，因为该属性在同一行上不能同时是Memory和Color，它们都可以是true但在不同的行上。 COUNT（DISTINCT attributes.name）计算Color或Memory的数字属性，如果它是2，那么第一个条件为真时至少有1行，而另一个条件为真，则为1行。
#1
2
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.
此解决方案使用GROUP BY子查询。您必须使用OR，因为该属性在同一行上不能同时是Memory和Color，它们都可以是true但在不同的行上。 COUNT（DISTINCT attributes.name）计算Color或Memory的数字属性，如果它是2，那么第一个条件为真时至少有1行，而另一个条件为真，则为1行。