python - 如何从他的 ManyToMany 关系中选择包含某些 child 的 parent ？

Question

给定这段代码(Python & TortoiseORM):

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)

如何查询同时包含 Ingredient.name="tomato"和 Ingredient.name="onion"的所有食谱？我相信在 Django-ORM 中，可以使用 & 运算符或 intersect 方法对 QuerySet 进行一些交集。

更新#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(
    <strong>ingredients__name='tomato'</strong>
).filter(
    <strong>ingredients__name='onion'</strong>
)

通过使用两个 .filter(…) [Django-doc]调用时，我们创建两个 LEFT OUTER JOIN，一个用于搜索tomato，另一个用于搜索onion。这显然只适用于 Django ORM，不适用于 Tortoise ORM。

如果我们使用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(
    <strong>ingredients__name__in=items</strong>
).alias(
    <strong>ncount=Count('ingredients')</strong>
).filter(<strong>ncount=len(items)</strong>)

或在 django-3.2 之前:

from django.db.models import Count

items = {'tomato', 'onion'}

Recipe.objects.filter(
    ingredients__name__in=items
).<strong>annotate(</strong>
    ncount=Count('ingredients')
<strong>)</strong>.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 <strong>COUNT(recipe_ingredients.ingredient_id) = 2</strong>

特别是 HAVING COUNT(recipe_ingredients.ingredient_id) 是这里的关键，因为 WHERE 子句已经将其过滤为仅洋葱和西红柿。

这要求成分的 name 是唯一的(即没有两个 Ingredient 记录具有相同的名称)。您可以使用以下方法使 name 字段唯一:

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