ORM filtering with nested joins

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!

It should be something like this.
(That second segment is ingredient because of the related_name attribute on the ForeignKey field.)

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.

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

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

Returns all IngredientAlias related to a specific Ingredient.

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.)


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

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!