CODEWITHSUNDEEP
django
Santhosh
Santhosh

Reputation: 11774

Django: How to do calculations when multiple models are linked together using annotation

I have the following Django models:

class Ingredient(models.Model):
    name = models.CharField(max_length=200)
    cost_per_kg = models.DecimalField(max_digits=19, decimal_places=10


class Recipe(models.Model):
    name = models.CharField(max_length=200)
    qty_in_kg = models.DecimalField(max_digits=19, decimal_places=10)
    #qty_in_kg quantity of preparation

class RecipeIngredients(models.Model):
    ingredient = models.ForeignKey(Ingredient)
    recipe = models.ForeignKey(Recipe)
    qty_in_kg_of_ing = models.DecimalField(max_digits=19, decimal_places=10)
    #qty_in_kg_of_ing: qty of ingredient in kg required to make the qty_in_kg of recipe

I want to get the total cost for making the Recipe.

How to get a queryset of recipes with extra column containing the total cost.

Upvotes: 1

Views: 473

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476574

We can annotate it as follows:

from django.db.models import F, Sum

subs = Sum(F('recipeingredients__qty_in_kg_of_ing') *
           F('recipeingredients__ingredient__cost_per_kg'))

qs = Recipe.objects.annotate(
    cost=F('qty_in_kg') * subs
)

This then results in query like:

SELECT r.*, (r.qty_in_kg * SUM((ri.qty_in_kg_of_ing * i.cost_per_kg))) AS cost
FROM recipe AS r
LEFT OUTER JOIN recipeingredients AS ri ON r.id = ri.recipe_id
LEFT OUTER JOIN ingredient AS i ON ri.ingredient_id = i.id
GROUP BY r.id

In case there are no ingredients in a recipe, the .cost will be None, not zero. So you need to take this into account when further processing the data.

Upvotes: 1

Nicolò Gasparini
Nicolò Gasparini

Reputation: 2396

Firstly, you are missing a field in the RecipeIngredient model, since there is no connection between Recipe and Ingredient at the moment there would be no way to collect all of the ingredients for a given Recipe. Here are the updated models with a foreign key recipe in the RecipeIngredient to solve the issue.

class Ingredient(models.Model):
    name = models.CharField(max_length=200)
    cost_per_kg = models.DecimalField(max_digits=19, decimal_places=10


class Recipe(models.Model):
    name = models.CharField(max_length=200)
    qty_in_kg = models.DecimalField(max_digits=19, decimal_places=10)
    #qty_in_kg quantity of preparation

class RecipeIngredients(models.Model):
    ingredient = models.ForeignKey(Ingredient)
    recipe = models.ForeignKey(Recipe)
    qty_in_kg_rec = models.DecimalField(max_digits=19, decimal_places=10)
    #qty_in_kg_rec: required to make qty_in_kg

The script you would need to run is the following:

cost = 0
# Loading recipe
recipe = Recipe.objects.get(name="recipe_name")
# Finding every connection between recipe and ingredients
connections = RecipeIngredients.objects.filter(recipe=recipe).all()
for rec_ing in connections:
    # Calculating every ingredient cost
    cost += rec_ing.ingrdient.cost_per_kg * rec_ing.qty_in_kg_rec
# Calculating cost of recipe
cost = cost * recipe.qty_in_kg

Upvotes: 2

Related Questions