I don't know what I messed up.
I tried so many methods and solutions and so far this works the best, but the ordering is messy. I want to sum the expenses grouped by the month extracted from the datefield it was given.
So far I made this:
def index(req):
qs = Expense.objects.filter(pom='OUT').annotate(month=TruncMonth('when')).values('month').annotate(total_amount=Sum('amount')) # pom='OUT' -- expense (there are expenses and incomes)
return render(req, 'index.html', {'data': qs})
which works perfectly, returns them as "sept. 15.000, aug. 10.000" which is nice! But the last/latest month is always at the bottom so I wanted to give it an order_by, which returns each unique object in the model (so if sept. 15.000 came up as sept.15 10.000 and sept.22 5.000, when they're not summed up but returned/shown separately).
What did I mess up?
EQ:
The mentioned query (without order_by) returns a queryset as of
<QuerySet [{'month': datetime.date(2022, 8, 1), 'total_amount': 15000.0}, {'month': datetime.date(2022, 9, 1), 'total_amount': 16000.0}]>
by default making it look they all have been recorded at the start of the given month.
Drop a reply for further information if I forgot to include something/explained something poorly!
CodePudding user response:
Try this:
Expense.objects.filter(pom='OUT')\
.values('when__year', 'when__month')\
.annotate(total_amount=Sum('amount'))\
.order_by('-when__year','-when__month')
I included the year, otherwise it will add total_amount of next years months to this years months. Remove the -
if you need an ascending order.
This will yield a result like:
<QuerySet [
{'when__year': 2022, 'when__month':9, 'total_amount': 15000.0},
{'when__year': 2022, 'when__month':8, 'total_amount': 16000.0}
]>
Edit:
Expense.objects.filter(pom='OUT')\
.values(
year=models.F('when__year'),
month=models.F('when__month')
).annotate(total_amount=Sum('amount'))\
.order_by('-year','-month')
This will yield:
<QuerySet [
{'year': 2022, 'month':9, 'total_amount': 16000.0},
{'year': 2022, 'month':8, 'total_amount': 15000.0}
]>