如何在PostgREST中按匹配食材数量选择并排序食谱？

开发简易Supabase应用时，需要构建PostgREST查询来筛选食谱，并按**匹配食材（matched_ingredients）**的数量升序排列。目前卡在该步骤，当前使用的查询语句如下：

https://URL.supabase.co/rest/v1/recipes?
select=*,ingredients:recipes_ingredients(*,ingredient_id(*))
&ingredients.ingredient_id=in.(92cd47bc-14a2-4d54-84d7-36e9ae96873f)

示例场景 #

假设用户冰箱中有鸡蛋、番茄和奶酪，执行查询后预期返回结果如下：

[
  {
    "id": "c89d8938-230c-11ed-861d-0242ac120002",
    "title": "Lasagne",
    "approximate_time": 90,
    "total_ingredients": 3,
    "matched_ingredient": 2,
    "ingredients": [
      ...
    ]
  },
  {
    "id": "8b38d376-230d-11ed-861d-0242ac120002",
    "title": "Pasta",
    "approximate_time": 20,
    "total_ingredients": 3,
    "matched_ingredient": 1,
    "ingredients": [
      ...
    ]
  }
]

相关表结构 #

Recipes表 #

Ingredients表 #

Recipe Ingredients表 #

解决方案 #

要实现需求，需要在查询中加入聚合计算来统计总食材数和匹配食材数，同时指定排序规则。以下是完整的PostgREST查询语句：

https://URL.supabase.co/rest/v1/recipes?
select=id,title,approximate_time,
total_ingredients:count(recipes_ingredients.id),
matched_ingredients:count(recipes_ingredients.id)filter(where recipes_ingredients.ingredient_id in.(bdd52b0e-230d-11ed-861d-0242ac120002,e886a0d0-230d-11ed-861d-0242ac120002,ebee3af8-230d-11ed-861d-0242ac120002)),
ingredients:recipes_ingredients(*,ingredient_id(*))
&group=id,title,approximate_time
&order=matched_ingredients.asc

关键说明 #

1. 聚合计算：

   • total_ingredients:count(recipes_ingredients.id)：统计每个食谱的总食材数量
   • count(...)filter(where ...)：仅统计用户拥有的食材（鸡蛋、番茄、奶酪）的匹配数量，别名matched_ingredients

2. 分组与排序：

   • group=id,title,approximate_time：必须指定分组字段，否则聚合函数无法正常工作
   • order=matched_ingredients.asc：按匹配食材数量升序排列结果

3. 可选筛选：如果需要只返回至少有一个匹配食材的食谱，可添加&having=matched_ingredients.gte.1参数。

内容的提问来源于stack exchange，提问作者spaceplacepl