按选项过滤产品 [英] Filter products by options

问题描述

我具有以下数据库结构来存储产品选项。

<现在我很难过滤出只匹配给定选项的产品。首先，我在WHERE option_id IN（选项数组）中进行了操作，但这会给我提供与任何选项匹配的产品，但这不是解决方案。例如，用户只想过滤具有给定材料，颜色和尺寸的产品。如果我在其中option_id = 4 AND option_id = 6的情况下却什么也没得到。

这是我的查询：

 选择不同的p.id AS ID，
 ... 
从产品p 
左加入product_categories pc ON p.id = pc.product_id 
左连接product_images pi开p.id = pi.product_id 
左连接product_options po开p.id = po.product_id 
其中p.product_active = 1 
和po.option_id = 1 //仅是获得想法
 GROUP BY ID 
 ORDER BY ID DESC 
 LIMIT 0，
 12 
  

这只是PHP应用程序，用户可以从具有或不具有多个属性的select元素中选择选项。

如何完成此操作？

解决方案

您可以使用具有：

  SELECT p.id AS id，... 
 FROM产品p加入
 product_categories pc 
在p.id上= pc.product_id左加入
 product_images pi 
 on p.id = pi.product_id加入
 product_options po 
 on p.id = po.product_id 
 W.p.product_active = 1和
 po.option_id IN（4，6）
 p.id 
的分组COUNT（DISTINCT po.option_id）= 2 
按p.id DESC排序
 LIMIT 0，12; 
  

HAVING 子句指定给定 id 有两个匹配的选项。由于 WHERE 子句，这是您唯一关心的两个选项。

我没有改变您的方法（您没有提供完整的查询），但是您正在沿着不同的维度进行联接-类别，图像和选项。这会为每个产品创建笛卡尔乘积，但这通常不是进行此类查询的最佳方法。

I have following database structure to store product options.

Now i have problem to filter out products that match only given options. First i did WHERE option_id IN (array of options), but that would give me products that match any of the options and that is not solution. User wants to filter out only products with given material, color, and size for instance. And if i do WHERE option_id = 4 AND option_id = 6 for instance i get nothing.

Here is my query:

SELECT DISTINCT p.id AS id,
                ...
FROM products p
LEFT JOIN product_categories pc ON p.id = pc.product_id
LEFT JOIN product_images pi ON p.id = pi.product_id
LEFT JOIN product_options po ON p.id = po.product_id
WHERE p.product_active = 1
  AND po.option_id = 1 // only to get the idea
GROUP BY id
ORDER BY id DESC
LIMIT 0,
      12

Just to mention it is PHP application , where user select options from select element with or without multiple attribute.

How to acomplish this?

解决方案

You can use having:

SELECT p.id AS id,  ...
FROM products p JOIN
     product_categories pc
     ON p.id = pc.product_id LEFT JOIN
     product_images pi
     ON p.id = pi.product_id JOIN
     product_options po
     ON p.id = po.product_id
WHERE p.product_active = 1 AND
      po.option_id IN (4, 6)
GROUP BY p.id
HAVING COUNT(DISTINCT po.option_id) = 2
ORDER BY p.id DESC
LIMIT 0, 12;

The HAVING clause is specifying that a given id has two matching options. Because of the WHERE clause, these are the only two options that you care about.

I didn't change your approach (you didn't supply the complete query), but you are doing joins along different dimensions -- categories, images, and options. This creates a Cartesian product for each product, and that is often not the best approach to such a query.

这篇关于按选项过滤产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！