Django - values() and group by month

Question

I am stuck with a problem and not getting solution anywhere.

columns in model are

category
date
amount

I need to get total of amount for each category and display in template for each month. Something like this

Category---month1---Month2
A           100       180
B           150       200

I have tried below query but it does not seem to work.

queryresult = model.objects.filter().values('category').annotate(total='amount')

It do give total for each category but how do I arrange it monthwise. Basically total of category for each month?

Edit: Trying to retrieve queryresult in below format:

categoryA{
month1{total}
month2{total}
}
categoryB{
month1{total}
month2{total}
}

Answer 1

Structuring query is not directly possible with Django ORM, you may need to structure it as per your requirement in python.

from itertools import groupby
from operator import itemgetter
from django.db.models.functions import ExtractMonth

# adjust the query set as per your requirements
quesryset = model.objects.values('category', month=ExtractMonth('date')).annotate(total=Sum('amount')).order_by(
    'category', 'month')

# Process query set in python to get desired output
result_dict = {}
for category, entries in groupby(quesryset, key=itemgetter('category')):
    result_dict[category] = [{'month': entry['month'], 'total': entry['total']} for entry in entries]