How to annotate a Django QuerySet aggregating annotated Subquery

Question

I have a few Django models with a FK relationship between them:

from django.db import models


class Order(models.Model):
    notes = models.TextField(blank=True, null=True)


class OrderLine(models.Model):
    order = models.ForeignKey(Order, on_delete=models.CASCADE)
    quantity = models.PositiveIntegerField()
    price = models.DecimalField(max_digits=8, blank=True, decimal_places=2)

Given an OrderLine you can calculate its total as quantity by price:

def get_order_line_total(order_line):
    return order_line.quantity * order_line.price

Given an Order you can calculate its total as the sum of its order lines totals:

def get_order_total(order):
    order_total = 0
    for orderline_for in order.orderline_set.all():
        order_total += (order_line_for.quantity * order_line_for.price)
    return order_total

I want to annotate that totals in querysets so I can filtrate them, sort them, etc.

For the OrderLine models I found it pretty straight forward:

from django.db.models import F, FloatField, Sum


annotated_orderline_set = OrderLine.objects.annotate(orderline_total=Sum(F('quantity') * F('price'), output_field=FloatField()))

Now I want to annotate the total in an Order.objects queryset. I guess I would need to use a Subquery but I can't make it work. My guess is (Not working):

from django.db.models import F, FloatField, OuterRef, Subquery, Sum


Order.objects.annotate(
    order_total=Subquery(
        OrderLine.objects.filter(
            order=OuterRef('pk')
        ).annotate(
            orderline_total=Sum(F('quantity') * F('price'), output_field=FloatField())
        ).values(
            'orderline_total'
        ).aggregate(
            Sum('orderline_total')
        )['orderline_total__sum']
    )
)

# Not working, returns:
# ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

How could I solve this?

Order.objects.annotate(orderline_total=Sum(F('orderline__quantity')*F('orderline__price'), output_field=FloatField())) — aedry
– aedry, Commented Mar 12, 2019 at 20:30
@aedry Your comment solved my problem, it's simpler than I imagined. Thank you! — Tadeo
– Tadeo, Commented Mar 13, 2019 at 4:46

djvg · Accepted Answer · 2021-07-05 07:23:26Z

3

As @aedry comment pointed out, a very simple solution avoiding the Subquery is:

Order.objects.annotate(total=models.Sum(F('orderline_set__quantity') * F('orderline_set__price'), output_field=models.DecimalField(max_digits=10, decimal_places=2)))

(I applied the output_field=DecimalField idea from @Todor answer for type consistency)

edited Jul 5, 2021 at 7:23

djvg

14.8k7 gold badges84 silver badges120 bronze badges

answered Mar 13, 2019 at 4:51

Tadeo

4817 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Todor · Accepted Answer · 2019-03-12 22:56:28Z

0

You cannot use .aggregate because this evaluates the queryset immediately while you need this evaluation to be delayed until the outer query is being evaluated.

So the correct approach is to .annotate instead of .aggregate.

class OrderQuerySet(models.QuerySet):
    def annotate_total(self):
        return self.annotate(
            total=models.Subquery(
                OrderLine.objects.filter(
                    order=models.OuterRef('pk')
                ).annotate_total()
                .values('order')
                .annotate(total_sum=models.Sum('total'))
                .values('total_sum')
            )
        )


class Order(models.Model):
    # ...
    objects = OrderQuerySet.as_manager()


class OrderLineQuerySet(models.QuerySet):
    def annotate_total(self):
        return self.annotate(
            total=models.ExpressionWrapper(
                models.F('quantity')*models.F('price'),
                output_field=models.DecimalField(max_digits=10, decimal_places=2)
            )
        )


class OrderLine(models.Model):
    #...
    objects = OrderLineQuerySet.as_manager()


# Usage:
>>> for l in OrderLine.objects.all().annotate_total():
...    print(l.id, l.order_id, l.quantity, l.price, l.total)
... 
1 1 3 20.00 60
2 1 9 10.00 90
3 2 18 2.00 36

>>> for o in Order.objects.all().annotate_total():
...    print(o.id, o.total)
... 
1 150
2 36

answered Mar 12, 2019 at 22:56

Todor

16.2k6 gold badges61 silver badges63 bronze badges

5 Comments

Tadeo Over a year ago

I tried your code and usage for OrderLine.objects.all().annotate_total() is working OK but for Order.objects.all().annotate_total() usage I'm getting KeyError: 'total' for Django 2.0.7 and ProgrammingError: more than one row returned by a subquery used as an expression for Django 2.1.7. Did you get it working? What Django version did you use? Thanks a lot for your help!

Todor Over a year ago

This error in not related to the version of Django. Its either data or queryset related, you can inspect the generated query with print(queryset.query) and debug why you get multiple rows for a subquery, any change you are missing the first .values('order') which will group by order?

Tadeo Over a year ago

Nop, I copied the code in your answer, did you get it working? Thanks a lot!

Todor Over a year ago

Yes, I have it working on a test project with 2 orders and 3 OrderLine's as you can see from the example usage. Again, debug your subquery, run it manually and you will see why you get multiple rows for a subquery.

Tadeo Over a year ago

Hey @Todor, thank you very much for your follow up. I make a super simple app with the minimum code required and it worked. After a little research I found that using Postgres is when your code throws "KeyError: 'total'".

Collectives™ on Stack Overflow

How to annotate a Django QuerySet aggregating annotated Subquery

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related