使用AND代替OR连接器，通过多个属性选择产品，数据模型EAV [英] Select products by multiple attributes, using AND instead OR concatenator, Data model EAV

问题描述

我在查询电子商务网站产品过滤器时遇到问题.

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

我有这样的EAV数据模型:

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]

我的查询:

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

问题是当我在>>和(attributes.name ="Memory" AND attribute_values.value ="16GB")子查询之间使用>>时，我得到0个结果，但是当我使用OR时，它给了我更多的结果我需要与这两个条件无关的东西.

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.

例如，当我收到请求时:内存= 16GB，颜色=黑色".我预计会获得iPhone 5 16BG黑色，而不是16GB的所有iPhone(金，深空灰等).

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.

关于， 安东

推荐答案

您的子查询应如下所示:

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

此解决方案使用GROUP BY子查询.您必须使用OR，因为同一行中的属性不能同时为Memory和Color，它们都可以为true，但可以在不同的行中. COUNT(DISTINCT attribute.name)计算Color或Memory的数量属性，如果为2，则至少有第一个条件为true的行，而另外一个条件也为true的行.

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.

这篇关于使用AND代替OR连接器，通过多个属性选择产品，数据模型EAV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！