webshop筛选器返回的不正确行

Question

我有一个名为products的表，其中包含字段ID、名称、URL、品牌，然后我有一个名为product_filter的字段，其中包含ID、PRODUCT_ID、TYPE、VALUE等字段。

假设我有以下products表

1件产品1件产品-1件牌

产品2产品2产品品牌2

3件产品3件产品-3件牌1

和product_filter表

1件1件材料

材料2，1,000

3×2×材料/棉

4-3-材料-羊毛

5/1季*秋季

5/2季*秋季

5%1季全部

等

现在，当客户在网页上并试图过滤时

材质:羊毛，棉花季节:秋季

我的结果是产品1(有材料和正确的季节)和产品2(有1种材料和正确的季节)。

我试了一下

SELECT DISTINCT(shop_product.product_number), `shop_product`.`color_count`, `shop_product`.`category_id`, `shop_product`.`in_stock`, `shop_product`.`url_image`, `shop_product_description`.* FROM (`shop_product`) JOIN `shop_product_description` ON `shop_product`.`id` = `shop_product_description`.`product_id` JOIN `shop_category_description` ON `shop_product`.`category_id` = `shop_category_description`.`category_id` INNER JOIN `shop_filters` ON `shop_product`.`id` = `shop_filters`.`product_id` WHERE `shop_product`.`status` = 1 AND `shop_product_description`.`language_id` = '1' AND `shop_category_description`.`language_id` = '1' AND ( (shop_filters.type = '1' AND shop_filters.keyword = 'cotton') OR (shop_filters.type = '1' AND shop_filters.keyword = 'wool') ) AND (shop_filters.type = '2' AND shop_filters.keyword = 'fall') ORDER BY shop_product`.`url_image` asc, `shop_product_description`.`name` desc LIMIT 36    

我用Distinct来拿回更多的产品(因为产品1有两种材料)。

但直到我改变了才能得到结果

( (shop_filters.type = '1' AND shop_filters.keyword = 'cotton') OR (shop_filters.type = '1' AND shop_filters.keyword = 'wool') ) AND (shop_filters.type = '2' AND shop_filters.keyword = 'fall')    

至

( (shop_filters.type = '1' AND shop_filters.keyword = 'cotton') OR (shop_filters.type = '1' AND shop_filters.keyword = 'wool') ) OR (shop_filters.type = '2' AND shop_filters.keyword = 'fall')    

然而，我的结果是与棉花或羊毛或秋季产品。如果有人有建议，那就请.

Answer 1

您需要多次匹配筛选器表，以检查每个筛选器。最简单的可能是做多个连接。

SELECT DISTINCT(shop_product.product_number), shop_product.color_count, shop_product.category_id, shop_product.in_stock, shop_product.url_image, shop_product_description.* 
FROM (shop_product) 
JOIN shop_product_description ON shop_product.id = shop_product_description.product_id 
JOIN shop_category_description ON shop_product.category_id = shop_category_description.category_id 
INNER JOIN shop_filters sf1 ON shop_product.id = sf1.product_id 
INNER JOIN shop_filters sf2 ON shop_product.id = sf2.product_id 
WHERE shop_product.status = 1 AND shop_product_description.language_id = '1' 
AND shop_category_description.language_id = '1' 
AND ( (sf1.type = '1' AND shop_filters.sf1 = 'cotton') OR (sf1.type = '1' AND sf1.keyword = 'wool') ) 
AND (sf2.type = '2' AND sf2.keyword = 'fall') 
ORDER BY shop_product.url_image asc, shop_product_description.name desc LIMIT 36