获取具有所需属性的产品 [英] Getting products with desired attributes

问题描述

我有一个包含产品和一组属性的类别. 这些属性是形状"和直径".

I have a category with products and a set of attributes. Those attributes are "Shape" and "Diameter".

当我打开一个类别而不应用过滤器时，我得到带有以下查询的产品:

When I open a category without applying filters I get products with following query:

"SELECT * FROM `products` 
WHERE `category_id` 
IN ('6', '7', '29', '8', '9', '36')"

然后，我单击属性直径"(id = 2)，值为"8mm"(id = 4) 我生成以下查询:

Then I click on attribute "Diameter"(id = 2) with value "8mm"(id = 4) I generate the following query:

"SELECT * FROM `products`
JOIN `products_attributes_values`
ON (`products_attributes_values`.`product_id` = `products`.`id`)
WHERE `category_id` IN ('6', '7', '29', '8', '9', '36')
AND `products_attributes_values`.`attribute_id` = 2
AND `products_attributes_values`.`value_id` = ('4')"

我得到了我需要的，所有带直径8毫米"的珠子 然后，我想进一步过滤我的产品，然后单击值为"Round"(id = 11)的属性"Shape"(id = 3) 而且我什么也没得到，查询看起来像:

And I get what I need, all beads with "Diameter 8mm" Then I want to filter my products more and click on attribute "Shape"(id = 3) with value "Round"(id = 11) And I get nothing, the query is look like:

"SELECT * FROM `products` 
JOIN `products_attributes_values`
ON (`products_attributes_values`.`product_id` = `products`.`id`)
WHERE `category_id` IN ('6', '7', '29', '8', '9', '36')
AND `products_attributes_values`.`attribute_id` = 2
AND `products_attributes_values`.`value_id` = ('4')
AND `products_attributes_values`.`attribute_id` = 3
AND `products_attributes_values`.`value_id` = ('11')"

所以我要获得直径8mm的圆形小珠而不是什么. 另外，所有过滤器都可以正常工作.当我想像上面的示例那样混合它们时，它只是不起作用. 我需要如何修改查询才能完成工作?

so instead of nothing I want to get beads with diameter of 8mm and round shape. Separately all filters works fine. It just not working when I want to mix them like in example above. How I need to modify the query to get the job done?

谢谢.

更新:

我想将一个属性的多个值与另一个或多个属性组合.

I want to combine multiple values on one attributes with one or more of another.

例如我想获得直径(id = 2)为8mm(id = 4)或直径(id = 2)10mm(id = 5)和Shape(id = 3)圆形(id = 11)的珠子)或Shape(id = 3)刻面(id = 62)

For instance I want to get Beads with Diameter(id=2) of 8mm(id=4) or Diameter(id=2) 10mm(id=5) and Shape(id=3) of Round(id=11) or Shape(id=3) Faceted(id=62)

所以最后我想得到8mm或10mm的圆形或多面形状的珠子.

So at end I want to get both 8mm or 10mm beads with round or faceted shape.

推荐答案

这2个过滤器需要分别处理，因为每个过滤器需要查看products_attributes_values表中完全不同的记录.

The 2 filters need to be handled separately, as each filter needs to look at completely different records in the products_attributes_values table.

SELECT * FROM products
WHERE category_id IN ('6', '7', '29', '8', '9', '36')
    AND id IN (
        SELECT product_id
        FROM products_attributes_values
        WHERE attribute_id = 2
            AND value_id = '4')
    AND id IN (
        SELECT product_id
        FROM products_attributes_values
        WHERE attribute_id = 3
            AND value_id = '11')

来自UAMoto:

它可以正常工作，但是有点慢，所以我使用了一个人的别名告诉我:

Its working but a bit slow, so I've used join aliases like one person told me:

"SELECT * FROM `products`
JOIN `products_attributes_values` AS pav1
ON (`pav1`.`product_id` = `products`.`id`)
JOIN `products_attributes_values` AS pav2
ON (`pav2`.`product_id` = `products`.`id`)
WHERE `category_id` IN ('6', '7', '29', '8', '9', '36')
AND (`pav1`.`attribute_id` = '2' AND `pav1`.`value_id` = '4')
AND (`pav2`.`attribute_id` = '3' AND `pav2`.`value_id` = '11')"

更新的属性组合查询:

SELECT * FROM products
WHERE category_id IN ('6', '7', '29', '8', '9', '36')
    AND id IN (
        SELECT product_id
        FROM products_attributes_values
        WHERE attribute_id = 2
            AND value_id IN ('4', '5'))
    AND id IN (
        SELECT product_id
        FROM products_attributes_values
        WHERE attribute_id = 3
            AND value_id IN ('11', '62'))

或

SELECT *
FROM `products`
    JOIN `products_attributes_values` AS pav1 ON (`pav1`.`product_id` = `products`.`id`)
    JOIN `products_attributes_values` AS pav2 ON (`pav2`.`product_id` = `products`.`id`)
WHERE `category_id` IN ('6', '7', '29', '8', '9', '36')
AND (`pav1`.`attribute_id` = '2' AND `pav1`.`value_id` IN ('4', '5'))
AND (`pav2`.`attribute_id` = '3' AND `pav2`.`value_id` IN ('11', '62'))

这篇关于获取具有所需属性的产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！