ORM filtering with nested joins

Using Django Using the ORM
1

Hey all!

I thought I was getting a hang of the django ORM but I can’t figure out how this query is supposed to be done.

My query involves the following three models:

class Recipe(models.Model):
    name = models.CharField(max_length=100)
    servings = models.FloatField(default=1)

class Ingredient(models.Model):
    name = models.CharField(max_length=100)

class RecipeLine(models.Model):
    parent_recipe = models.ForeignKey(Recipe, related_name='recipe_lines', on_delete=models.CASCADE)
    ingredient = models.ForeignKey(Ingredient, related_name='ingredient', on_delete=models.SET_NULL, blank=True, null=True)
    number = models.FloatField(blank=True, default=0)
    unit = models.CharField(max_length=100, blank=True, default='')

class IngredientAlias(models.Model):
    parent_ingredient = models.ForeignKey(Ingredient, related_name='aliases', on_delete=models.CASCADE)
    alias = models.CharField(max_length=100)

In my app a user navigates to a recipe page that displays a recipe with all the associated recipe_lines which all have associated ingredients. What I’d like to do is gather all the ingredient_aliases for all the ingredients for all the recipe_lines given a recipe.

The raw SQL of this query looks like this:

select ingredientalias.alias, ingredientalias.id, ingredientalias.parent_ingredient_id
from ingredientalias 
inner join ingredient on ingredientalias.parent_ingredient_id = ingredient.id 
inner join recipeline on recipeline.ingredient_id = ingredient.id
inner join recipe on recipe.id = recipeline.parent_recipe_id
where recipe.id = '{recipe.id}'

As of now I’m using .raw() for this particular endpoint but I’d really like to know if/how this can be done with the ORM.

thank you and please let me know if you need any more information!

2

It should be something like this.
IngredientAlias.objects.filter(parent_ingredient__ingredient__recipe=recipe)
(That second segment is ingredient because of the related_name attribute on the ForeignKey field.)

3

Thank you Ken! I may not have done a good enough job explaining my problem.

ingredient can be found through parent_ingredient__ingredient but ingredient is only connected to recipe through recipe -> recipeline -> ingredient. The full ‘path’ would be something like recipe -> recipeline -> ingredient -> ingredientalias.

If I understand correctly your solution would work only if ingredient had a ForeignKey pointing towards recipe.

4

Yes, that third step should have used parent_recipe, not recipe.

Let’s look at this step-by-step.

IngredientAlias.objects.filter(parent_ingredient=some_ingredient)
Returns all IngredientAlias related to a specific Ingredient.

IngredientAlias.objects.filter(parent_ingredient__ingredient=some_recipeline)
Returns all IngredientAlias related to a specific RecipeLine. (The first reference is to Ingredient by the name parent_ingredient, this second hop is then to RecipeLine via the related name ingredient in the ForeignKey field of RecipeLine.)

IngredientAlias.objects.filter(parent_ingredient__ingredient__parent_recipe=some_recipe)

This then is the reference to all IngredientAlias related to the Recipie named recipe.

5

Ah! Thank you for walking me through that. I definitely did not appreciate how smart the django ORM was in parsing foreign key relationships.

You’ve once again saved the day on the django forums, thank you again!