Sum on a subquery used in annotate

[harsh kraken]

I started a new project to get a bit back to development. I have a Recipe model, an IngredientGroup model, an Ingredient model, an IngredientDetail model and a Stock model (also a StockGroup model, which is not relevant here). Below are the relevant model definitions:

Link to models.py

I use DRF for the API, and I would like to make an endpoint where the recipes are ordered by how many ingredients are missing from stock compared to ingredients. I've tried to make a query, but it works partly, see the below:

Link to views.py

There are 2 nested subqueries. The first gets the stock for a current ingredient (same ingredient might be on stock more than once, hence the need to group by ingredient_id and sum on the amount. The next query uses the first subquery to compare the total required (again, grouping on ingredient_id and summing on amount), then annotating the IngredientDetail so it's 1 if total_available < total_required and 0 otherwise.

Up until then, this works, if I manually switch out the OuterRef('pk') I get the expected result.

With some test data (recipe 1: 1 ingredient is missing, recipe 2: 2 ingredients are missing, I get the following output from print(total_required_per_ingredient_subquery, total_required_per_ingredient_subquery.aggregate(Sum('is_missing')))

<QuerySet [{'is_missing': 1}, {'is_missing': 0}, {'is_missing': 0}, {'is_missing': 0}, {'is_missing': 0}, {'is_missing': 0}]> {'is_missing__sum': 1}

<QuerySet [{'is_missing': 1}, {'is_missing': 1}, {'is_missing': 0}, {'is_missing': 0}, {'is_missing': 0}]> {'is_missing__sum': 2}

There's 1 object per ingredient in the recipe.

My question is, how do I properly incorporate the second subquery into the last query to annotate each recipe with the amount of missing ingredients?

[vivid walrus]

Do I understand correctly that you get the correct result and then want to add/update it to the recipe data record?

[harsh kraken]

At least thats how I understand it. When I run the subquery manually by its own by adding a recipe_id like so instead of using the OuterRef:

        total_required_per_ingredient_subquery = IngredientDetail.objects.filter(
            ingredient_group__recipe_id=2
        ).values('ingredient_id').annotate(total_required=Sum('amount')).values('total_required').annotate(
            total_available=Subquery(total_stock_per_ingredient_subquery)
        ).annotate(
            is_missing=Case(
                When(total_available__lt=F('total_required'), then=1),
                default=0,
                output_field=IntegerField()
            )
        ).values('is_missing')

I get an output like this:

<QuerySet [{'is_missing': 1}, {'is_missing': 1}, {'is_missing': 1}, {'is_missing': 1}, {'is_missing': 0}]>

And this matches with my test data right now where 4 of the ingredients are missing for this recipe, and one has sufficient stock.
So for this one, I would like to add a missing_ingredients annotation that equals 4

[vivid walrus]

ah ok I see... how about a count if “is_missing” = 1?

[vivid walrus]

maybe something like this:

recipe_with_missing_ingredients = Recipe.objects.annotate(
    missing_ingredients=Count(
        Case(
            When(
                ingredientdetail__ingredient_group__recipe_id=OuterRef('pk'),
                ingredientdetail__is_missing=1,
                then=1
            ),
            output_field=IntegerField()
        )
    )
)

(not tested)

[harsh kraken]

I'm not sure how to run that since in your example now the total_required_per_ingredient_subquery which defines the is_missing is not used in the query