CODEWITHSUNDEEP
pythondjango
Dave
Dave

Reputation: 533

Return a QuerySet of lists grouped by date

I have a Transaction model in my database which stores all of my banking transactions and displays them on a page. They are all listed but the list is very long. To shorten it, I want to group them by month/year so I can create a collapsible list on the page with all months collapsed except for the current month. A long search only returns the aggregate and annotate functions (which I don't fully understand). The closest I've gotten is this:

>>> data = Transaction.objects.annotate(month=TruncMonth('date')).values('month').annotate(c=Count('id')).values('month', 'c')
>>> data
<QuerySet [{'month': datetime.date(2016, 12, 1), 'c': 74}, {'month': datetime.date(2017, 1, 1), 'c': 109}, {'month': datetime.date(2017, 2, 1), 'c': 70}, {'month': datetime.date(2017, 3, 1), 'c': 92}, {'month': datetime.date(2017, 4, 1), 'c': 79}, {'month': datetime.date(2017, 5, 1), 'c': 79}, {'month': datetime.date(2017, 6, 1), 'c': 83}, {'month': datetime.date(2017, 7, 1), 'c': 99}, {'month': datetime.date(2017, 8, 1), 'c': 98}, {'month': datetime.date(2017, 9, 1), 'c': 112}, {'month': datetime.date(2017, 10, 1), 'c': 87}, {'month': datetime.date(2017, 11, 1), 'c': 82}, {'month': datetime.date(2017, 12, 1), 'c': 86}, {'month': datetime.date(2018, 1, 1), 'c': 113}, {'month': datetime.date(2018, 2, 1), 'c': 98}, {'month': datetime.date(2018, 3, 1), 'c': 121}, {'month': datetime.date(2018, 4, 1), 'c': 111}, {'month': datetime.date(2018, 5, 1), 'c': 107}, {'month': datetime.date(2018, 6, 1), 'c': 91}, {'month': datetime.date(2018, 7, 1), 'c': 97}, '...(remaining elements truncated)...']>

As you can see, this just counts the transactions which occurred on the same month/year. I want to actually have all the transactions listed out.

Here is my Transaction model:

class Transaction(models.Model):
   date = models.DateField(default=datetime.date.today)
   description = models.CharField(max_length=100)
   category = models.CharField(max_length=100)
   amount = models.DecimalField(max_digits=10, decimal_places=2)

   def __str__(self):
       return self.description

Upvotes: 2

Views: 35

Answers (1)

Ralf
Ralf

Reputation: 16505

One solution is to do the grouping in the view.

You could iterate over your queryset in your view and assign the records to sublists per year/month/whatever; then pass those sublists to your template for rendering. 

import collections

data = collections.defaultdict(list)
for t in Transaction.objects.all():
    group = (t.date.year, t.date.month)
    data[group].append(t)

In this code snippet, the variable data is a dict that has year-month as its keys and lists of Transaction (corresponding to that year and month) as the values.

That way you avoid a complex query if you are going to render all the records anyways.

Upvotes: 1

Related Questions