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?
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]
)
)