Django - Annotating an aggregation of annotations on related objects

Question

I have three models:

class BaseModel(Model):
    deleted = BooleanField(default=False)


class Document(BaseModel):
    def total_price()
        return DocumentLine.objects.filter(
            section__in=self.sections.filter(deleted=False),
            deleted=False,
        ).total_price()


class Section(BaseModel):
    document = ForeignKey(Document, on_delete=CASCADE, related_name='sections')


class LineQuerySet(QuerySet):
    def with_total_price(self):
        total_price = F('quantity') * F('price')
        return self.annotate(
            total_price=ExpressionWrapper(total_price, output_field=DecimalField())
        )
    
    def total_price(self):
        return self.with_total_prices().aggregate(
            Sum('total_price', output_field=DecimalField())
        )['total_price__sum'] or Decimal(0.0)


class Line(BaseModel):
    objects = LineQuerySet.as_manager()
    section = ForeignKey(Section, on_delete=CASCADE, related_name='lines')

    price = DecimalField()
    quantity = DecimalField()

As you can see on the LineQuerySet, there is a method that will annotate the queryset with the total price of each line, based on the price and quantity.

Now I can easily get the total price of an entire document doing something like this (Note that lines and sections with deleted=True are ignored):

document = Document.objects.get(pk=1)
total_price = document.total_price()

However, now I would like to generate a queryset of multiple documents, and annotate that with each document's total price. I've tried a combination of annotates, aggregates, making use of prefetch_related (using Prefetch), and OuterRef, but I can't quite seem to be able to get the result I want without it throwing an error.

Is there some way to perform this operation in a queryset, making it then possible to filter or order by this total_price field?

willeM_ Van Onsem · Accepted Answer · 2021-11-16 16:30:13Z

1

You can annotate with:

from django.db.models import F, Sum

Document.objects.filter(
    deleted=False,
    sections__deleted=False,
    section__lines__deleted=False
).annotate(
    total_price=Sum(F('sections__lines__price')*F('sections__lines__quantity'))
)

Each Document that arises from this queryset will have an attribute .total_price which is the sum of the price times the quantity of all related lines of all related sections of that Document.

An alternative is to work with a Subquery expression [Django-doc] to determine the sum, so:

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

Document.objects.annotate(
    total_price=Subquery(
        Line.objects.values(
            document_id=F('section__document_id')
        ).filter(
            deleted=False, section__deleted=False, document__deleted=False
        ).annotate(
            total_price=Sum(F('price') * F('quantity'))
        ).order_by('document_id').filter(document_id=OuterRef('pk')).values('total_price')[:1]
    )
)

edited Nov 16, 2021 at 16:30

answered Nov 15, 2021 at 17:31

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

4 Comments

Swan Over a year ago

That gets me closer to the result I want, but it looks like I've left out some crucial information. I've updated the post. Each model also has a deleted flag, which when True means it should be ignored from the total_price. If a linegroup is deleted, all related lines should also be ignored, whether they're deleted or not.

Swan Over a year ago

That's almost there. The only problem now is that a document with a single section that is deleted will not be included in the queryset, because the filter is not satisfied for that document. Same with a document that has a section, but no lines in that section.

Swan Over a year ago

I finally got it to work using your last edit. I did need to add another .values('total_price') to filter out the document_id, to prevent an error of multiple columns returned. One thing I don't quite understand is the Sum annotation on the Line queryset. Does this add the result of the sum to every row in the Line result query? After which you just select the first one?

willeM_ Van Onsem Over a year ago

@Swan: we make a GROUP BY on the document_id in the subquery, and thus it will determine the sum of that group through .annotate(..).

Collectives™ on Stack Overflow

Django - Annotating an aggregation of annotations on related objects

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related