How to select parent that contains certain children from his ManyToMany relation?

user_14619191 0
Viewed 489

Given this piece of code (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)

How to query all recipes that contain BOTH Ingredient.name="tomato" and Ingredient.name="onion" ? I believe that in Django-ORM it was possible to make some intersection of QuerySets using & operator or intersect method.

Update #1
This query worked but it's a bit messy in my opinion and it's gonna be problematic when I will want to f.e. query all recipes that contain more than 2 ingredients.

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

Update #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'
tortoise-orm django-orm sql python
1 Answers

You can filter with:

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

By using two .filter(…) [Django-doc] calls, we make two LEFT OUTER JOINs, one where we search for tomato, and one for onion. This apparently only works with the Django ORM, not the Tortoise ORM.

If we use print(qs.query) (to construct the query), we obtain:

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

Another option is to make a single LEFT OUTER JOIN, and check if the number of items matches with the number of items, so:

from django.db.models import Count

items = {'tomato', 'onion'}

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

or prior to 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))

this thus provides a query that looks like:

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

Especially the HAVING COUNT(recipe_ingredients.ingredient_id) is key here, since the WHERE clause already filtered this down to only onions and tomatos.

This requires that the name of the ingredients is unique (i.e. there are no two Ingredient records with the same name). You can make the name field unique with:

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