[Answered ]-Django – Annotating an aggregation of annotations on related objects

by

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]
    )
)
👤willeM_ Van Onsem

Leave a comment