获取具有所需属性的产品 [英] 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屋!