根据SQL中的另一列值为列指定条件运算符(和/OR)

Question

我有一个recipe表，多到多到一个recipe_filter表。以下是一些样本数据：

recipe

id |   name
----+-----------
  1 | test 2019
 12 | slug-14
  8 | dfadsfd
  6 | test 4
  4 | test 2
 11 | slug-11
 10 | Testology
 13 | slug-15
  5 | test 3
 14 | slug-16
(10 rows)

recipe_filter_join

 recipeId | recipeFilterId
----------+----------------
        1 |              1
        2 |              2
        3 |              3
        4 |              1
        6 |              5
        7 |              6
        8 |              4
        9 |              7
        6 |              8
       14 |              9
       14 |              4
        5 |              9
        5 |             38

filter

id |         slug         |    name     |     label
----+----------------------+-------------+----------------
  2 | fdsfa                | fdsfa       | Category
  3 | dsfds                | dsfds       | Category
  6 | fdsaf                | fdsaf       | Category
  7 | dfad                 | dfad        | Category
  8 | product-spice-2      | Spice #2    | Product
  9 | product-spice-3      | Spice #3    | Product
  5 | product-spice-4      | Spice #4    | Product
  4 | product-spice-5      | Spice #5    | Product
  1 | product-spice-6      | Spice #6    | Product
 10 | product-spice-1      | Spice #1    | Product
 40 | diet-halal           | Halal       | Diet
 38 | diet-keto            | Keto        | Diet
 41 | diet-gluten-free     | Gluten free | Diet
 37 | diet-vegan           | Vegan       | Diet
 39 | diet-diabetic        | Diabetic    | Diet
 42 | cooking-method-bake  | Bake        | Cooking method
 43 | cooking-method-fry   | Fry         | Cooking method
 44 | cooking-method-steam | Steam       | Cooking method
 45 | cooking-method-roast | Roast       | Cooking method
(19 rows)

我的查询的输入是一个filters.slug的列表，例如product-spice-1、product-spice-5、cooking-method-fry、cooking-method-steam。

对于上面的示例，我想编写一个查询，获取筛选器段塞(product-spice-1或product-spice-5)和(cooking-method-fry或cooking-method-steam)所在的所有菜谱。

如何从上面的示例创建通用查询？

Update：如果不清楚，对于给定的filter列表，我希望基于label对它们进行分组，并在组成员之间应用OR和其他组的一个和条件(如果这有任何意义的话)。

Answer 1

您想要INTERSECT两个查询

SELECT
rfj."recipeId"
FROM recipe_filter_join rfj
JOIN filter ON filter.id = rfj."recipeFilterId"
WHERE filter.slug IN ('product-spice-1','product-spice-5')
INTERSECT
SELECT
rfj."recipeId"
FROM recipe_filter_join rfj
JOIN filter ON filter.id = rfj."recipeFilterId"
WHERE filter.slug IN ('cooking-method-fry', 'cooking-method-steam')

这是相当普遍的。正如您所看到的，这两个部分之间唯一的区别是WHERE子句。如果您对Diet或类别有其他条件，您可以使用filer上的变体生成适当的查询字符串&用INTERSECT作为您所选择的编程语言中的分隔符加入它们。

我想根据标签对它们进行分组，并在组成员之间应用OR和其他组的一个和条件。

如果您想让您的应用程序代码只使用一串子弹来调用查询，那么下面的解决方案就更通用了。

如果我们将问题描述重新描述为：

我们想要搜索含有与提供的成分列表相交的成分的食谱，并且菜谱的不同标签等于从成分列表中派生出来的不同标签(最后一部分由with子句处理)

我们可以写

WITH distinct_labels AS (
  SELECT
  ARRAY_AGG(DISTINCT label ORDER BY label) distinct_labels_filtered 
  FROM filter
  WHERE slug IN ('product-spice-1','product-spice-5','cooking-method-fry', 'cooking-method-steam')
)

  SELECT
  rfj."recipeId"
  FROM filter
  JOIN recipe_filter_join rfj
    ON filter.id = rfj."recipeFilterId"
  WHERE slug IN ('product-spice-1','product-spice-5','cooking-method-fry', 'cooking-method-steam')
  GROUP BY 1
  HAVING ARRAY_AGG(DISTINCT label ORDER BY label) = (SELECT distinct_labels_filtered FROM distinct_labels)