Django / Python - Querying calculated field

Question

I have the below models, and want to run a calculation on a foreign key of allocation and then sum the amounts.

class Fundraising(models.Model):
    @property
    def amount_raised(self):
        amount_raised = FundraisingDonation.objects.filter(
                            fundraising_event=self,
                            ).aggregate(donationamount=Coalesce(Sum('donationamount'), 0.00))['donationamount']
        return amount_raised

class FundraisingDonation(models.Model):
    donationamount = models.DecimalField(max_digits=11, decimal_places=2, default=0)


class Testmodel(models.Model):
      organisation = models.ForeignKey(Organisation, on_delete=models.CASCADE)
      allocation = models.ForeignKey(Fundraising, on_delete=models.CASCADE, related_name='items')
      percentshare = models.DecimalField(max_digits=11, decimal_places=2, default=0) #SET BY USER

     @property
     def amount(self):
         amount = self.allocation.amount_raised * self.percentshare
         return amount

The 'amount' model property above calculates the amount field for each model instance.

I'm trying now to sum up the amounts for each organisation on a new model, however the following doesn't work as I don't believe I can run a query on a calculated field?

class Totals(models.Model):
    totals = total + totalB + totalC
    @property
    def total(self):
        total = Testmodel.objects.filter(
                           organisation=self.organisation
                            ).aggregate(amount=Coalesce(Sum('amount'), 0.00))['amount']

Is there any way to amend the last line for this to work, or to reperform the calculation in this line? I also tried aggregate(Sum(amount_raised * percentshare)), but this didn't seem to work either?

Answer 1

You can not aggregate on a @property, since that property is unknown at the database side.

What you can do is .annotate(…) ^[Django-doc] the queryset of Organisations for example:

from django.db.models import F, Sum

Organisation.objects.annotate(
    total=Sum(
        F('testmodel__allocation__amount_raised') * F('testmodel__percentshare')
    )
)

Each Organisation that arises from this queryset will have an extra attribute named .total that contains the sum of the amount_raised values for the related testmodel items, multiplied with percentshare.

EDIT: since the amount_raised is a property, we need to aggregate on the FundraisingDonation model, so:

from django.db.models import F, Sum, Value
from django.db.models.functions import Coalesce

Organisation.objects.annotate(
    total=Coalesce(Sum(
        F('testmodel__allocation__fundraising__donationamount') *
        F('testmodel__percentshare'), Value(0))
    )
)