如何从多个关系中选择包含特定子项的父项？

Question

给出这段代码（Python&；Ortoiseorm）：

class Recipe(Model):
    description = fields.CharField(max_length=1024)
    ingredients = fields.ManyToManyField(model_name="models.Ingredient", on_delete=fields.SET_NULL)


class Ingredient(Model):
    name = fields.CharField(max_length=128)

如何查询同时包含配料.name=“西红柿”和配料.name=“洋葱”的所有食谱？ 我相信在Django ORM中，使用&；运算符或相交方法

更新#1
这个查询很有效，但在我看来有点混乱，当我想要f.e.查询所有包含2种以上成分的食谱时，这将是一个问题

subquery = Subquery(Recipe.filter(ingredients__name="onion").values("id"))  
await Recipe.filter(pk__in=subquery , ingredients__name="tomato")  

更新#2

SELECT "recipe"."description",
       "recipe"."id"
FROM   "recipe"
       LEFT OUTER JOIN "recipe_ingredient"
                    ON "recipe"."id" = "recipe_ingredient"."recipe_id"
       LEFT OUTER JOIN "ingredient"
                    ON "recipe_ingredient"."ingredient_id" = "ingredient"."id"
WHERE  "ingredient"."name" = 'tomato'
       AND "ingredient"."name" = 'onion'

Answer 1

您可以使用以下选项进行筛选：

Recipe.objects.filter(
    ingredients__name='tomato'
).filter(
    ingredients__name='onion'
)

通过使用两个^{} [Django-doc]调用，我们创建了两个^{{}，一个搜索tomato，一个搜索onion。这显然只适用于Django甲虫，而不适用于乌龟甲虫

如果我们使用print(qs.query)（构造查询），我们将获得：

SELECT recipe.id, recipe.description
FROM recipe
INNER JOIN recipe_ingredients ON recipe.id = recipe_ingredients.recipe_id
INNER JOIN ingredient ON recipe_ingredients.ingredient_id = ingredient.id
INNER JOIN recipe_ingredients T4 ON recipe.id = T4.recipe_id
INNER JOIN ingredient T5 ON T4.ingredient_id = T5.id
WHERE ingredient.name = tomato
  AND T5.name = onion

另一个选项是制作一个单LEFT OUTER JOIN，并检查项目数是否与项目数匹配，以便：

from django.db.models import Count

items = {'tomato', 'onion'}

Recipe.objects.filter(
    ingredients__name__in=items
).alias(
    ncount=Count('ingredients')
).filter(ncount=len(items))

或在django-3.2之前：

from django.db.models import Count

items = {'tomato', 'onion'}

Recipe.objects.filter(
    ingredients__name__in=items
).annotate(
    ncount=Count('ingredients')
).filter(ncount=len(items))

因此，这提供了一个如下所示的查询：

SELECT recipe.id, recipe.description
FROM recipe
INNER JOIN recipe_ingredients ON recipe.id = recipe_ingredients.recipe_id
INNER JOIN ingredient ON recipe_ingredients.ingredient_id = ingredient.id
WHERE ingredient.name IN (onion, tomato)
GROUP BY recipe.id
HAVING COUNT(recipe_ingredients.ingredient_id) = 2

尤其是这里的HAVING COUNT(recipe_ingredients.ingredient_id)是关键，因为WHERE子句已经将其过滤为洋葱和番茄

这要求成分的name是唯一的（即没有两个Ingredient记录具有相同的名称）。您可以使用以下命令使name字段唯一：

class Ingredient(Model):
    name = fields.CharField(unique=True, max_length=128)