Django - Annotating an aggregation of annotations on related objects-CodePudding

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?

CodePudding user response：

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'))[:1]
    )
)