sql - 一个查询中的 PostgreSQL 条件结合和分离

Question

如何创建一个查询来选择给定特征的产品，其中特征语句由“and”或“or”条件组成，具体取决于它们所属的组？

情况描述

1. 有一家商店。
2. 产品可能有也可能没有。
3. 客户寻找产品的特定功能，这意味着填写表单并发送一组功能 ID。
4. 在数据库中，每个特征只属于一组特征。
5. 第一组(disjunction 属性为真，称为“OR”)允许在其中一个特征与客户提交的任何特征匹配时显示产品。
   示例:选择形状:圆形、方形、三角形显示圆形或正方形或三角形的产品。
6. 第二组(disjunction 属性为 false，称为“AND”)允许仅当产品具有客户提交的所有功能时才显示该产品。
   示例:选择颜色:红色、绿色、蓝色显示红色和绿色和蓝色的产品。

测试环境

http://sqlfiddle.com/#!12/f4db7

“或”查询
它适用于那些没有功能的产品。

SELECT product_id 
FROM product_features 
WHERE product_features.feature_id IN ( 
    SELECT feature_id FROM features 
    LEFT JOIN feature_groups 
        ON features.feature_group_id = feature_groups.feature_group_id 
    WHERE feature_id IN (11, 12, 13) AND feature_groups.disjunction = TRUE 
)
GROUP BY product_id

“AND”查询
无法使用此查询，因为析取为假的要素数量未知。

SELECT product_id FROM product_features
WHERE feature_id IN (43, 53, 63)
GROUP BY product_id
HAVING COUNT(DISTINCT feature_id) = 3

Answer 1

“或”情况

更简单更快:

SELECT DISTINCT pf.product_id 
FROM   product_features    pf
LEFT   JOIN features       f  USING (feature_id)
LEFT   JOIN feature_groups fg USING (feature_group_id)
WHERE (f.feature_id = ANY (_my_arr) 
AND    fg.disjunction)
OR     _my_arr  = '{}';

... 其中 _my_arr 可以是 '{11, 12, 13}'::int[] 或 '{}'::int[ ]。如果 _my_arr 将是 NULL，请改用 _my_arr IS NULL。

• 由于 operator precedence AND 绑定(bind)在 OR 之前，不需要括号。不过，它们可能会提高可读性。

• DISTINCT 或 GROUP BY .. 在这里都很好。

• AND fg.disjunction ..因为这是一个 bool 类型，你可以缩短语法。

• JOIN 通常比另一个 IN 子句更快。

• USING 只是一种符号快捷方式，适用于您的(有用的!)命名约定。

或者，甚至更快(对于超过 1 个特征)- 并且更容易拆分案例:

SELECT product_id
FROM   products p
WHERE  EXISTS (
   SELECT 1
   FROM   product_features pf
   JOIN   features         f  USING (feature_id)
   JOIN   feature_groups   fg USING (feature_group_id)
   WHERE  pf.product_id = p.product_id
   AND    f.feature_id = ANY (_my_arr)
   AND    fg.disjunction
   )
OR     _my_arr  = '{}';

我宁愿根据输入(有功能/无功能)在您的应用中拆分大小写。与第二种形式无关紧要。

“与”情况

这是关系划分的经典案例。我们已经组装了一整套查询技术来处理这个相关问题:
How to filter SQL results in a has-many-through relation

可能是:

SELECT product_id
FROM   product_features p1
JOIN   product_features p2 USING (product_id)
JOIN   product_features p3 USING (product_id)
...
WHERE  p1.feature_id = 43
AND    p2.feature_id = 53
AND    p3.feature_id = 63
...

我在示例中忽略了 NOT feature_groups.disjunction，因为它也不在问题中。有需要就加吧。
我会在构建查询之前选择有效的 feature_id。

-> SQLfiddle demo.