Django filtering aggregate query after aggregation-CodePudding

I've got a transaction model:

class transaction(models.Model):
    transactiondate = models.DateField()
    details = models.CharField(max_length=200, null=True)
    amount = models.DecimalField(decimal_places=2, max_digits=10)
    accountid = models.ForeignKey(account, on_delete=models.PROTECT)

I'm trying to get a running total balance for each date so that I can plot the last 12 months balance on a line chart. My query is:

transaction.objects.annotate(
            balance=Window(Sum('amount'), order_by=F('transactiondate').asc())).filter(transactiondate__gte='2021-01-01')

The problem is that this isn't including data prior to 2021-01-01 in the calculation. I want the Sum to include all transactions regardless of date but I only want to see 12 months in my chart. I thought that tagging on the filter after the aggregate would calculate the correct balance first and then apply the filter to only give me 12 months, but that's not what's happening. If I remove the filter then I get the correct balance, but my line chart is unreadable with so much data.

Is there a way to filter the data after aggregation?

CodePudding user response：

Something like the following should work using a Subquery. The performance may be worse than using a window expression

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

previous_transactions = Transaction.objects.filter(transactiondate__lte=OuterRef('transactiondate')).order_by().values('amount')
balances = previous_transactions.annotate(balance=Sum('amount')).values('balance')
annotated = Transaction.objects.order_by('transactiondate').annotate(balance=Subquery(balances)).filter(transactiondate__gte='2021-01-01')

CodePudding user response：

Django does the group by using values. So I think you need to group by on each date you can use the following query.

import datetime
from django.db.models import Sum
from django.utils import timezone

transaction.objects.filter(
  transactiondate__gte=timezone.now().date()-timedeleta(months=12) # previous twelve months data
).values(
  'transactiondate' # this does the group by on date
).annotate(
  balance = Sum('amount') # aggregates the amount on each date
).values('transactiondate', 'balance')