Django-native/ORM based approach to self join

user_13421357 0
Viewed 52

Trying to set up a Django-native query that grabs all rows/relationships when it shows up on the other side of many-to-many relationship.

I can explain with an example:

# Django models

class Ingredient:
    id = ...
    name = ...
    ...

class Dish:
    id = ...
    name = ...
    ...

class IngredientToDish
    # this is a many to many relationship
    ingredient_id = models.ForeignKey("Ingredient", ...)
    dish_id = models.ForeignKey("Dish", ...)
    ...

I'd like a Django-native way of: "For each dish that uses tomato, find all the ingredients that it uses". Looking for a list of rows that looks like:

(cheese_id, pizza_id)
(sausage_id, pizza_id)
(tomato_id, pizza_id)
(cucumber_id, salad_id)
(tomato_id, salad_id)

I'd like to keep it in one DB query, for optimization. In SQL this would be a simple JOIN with itself (IngredientToDish table), but couldn't find what the conventional approach with Django would be... Likely uses some form of select_related but haven't been able to make it work; I think part of the reason is that I haven't been able to succinctly express the problem in words to come across the right documentation during research.

django-queryset django-models django python
1 Answers

You can .filter(…) [Django-doc] with:

Ingredient.objects.filter(
    ingredienttodish__dish_id__ingredienttodish__ingredient_id__name='Tomato'
)

You can also add the primary key of the dish for which this holds with:

from django.db.models import F

Ingredient.objects.filter(
    ingredienttodish__dish_id__ingredienttodish__ingredient_id__name='Tomato'
).annotate(
    dish_id=F('ingredienttodish__dish_id')
)

The Ingredient objects that arise from this QuerySet will have an extra attribute dish_id that contains the primary key of the Dish for which these were used.

Note: Normally one does not add a suffix …_id to a ForeignKey field, since Django will automatically add a "twin" field with an …_id suffix. Therefore it should be dish, instead of dish_id.

user_675790
Related