Creating a summary of objects

Using Django
1

I’m trying to create a summary of objects from the following model:

 class Purchase(models.Model):
    item = models.CharField(max_length=250, blank=False)
    price = models.IntegerField()
    date = models.DateField()
    store = models.CharField(max_length=250, blank=True)
    location = models.CharField(max_length=250, blank=True)
    category = models.CharField(max_length=250, blank=False)
    subcategory = models.CharField(max_length=250, blank=True)
    notes = models.TextField(blank=True)
    purchaser = models.ForeignKey(
        get_user_model(),
        on_delete=models.CASCADE,
    )

    def __str__(self):
        return self.item

    class Meta:
        ordering = ['-date']

I want the summary to show the total sum of the price of all purchases for a given category in a given month and given year. Something like

Year_1:
       Month_1:
               Category_1: Total
               Category_2: Total
Year_2:
       Month_2:
               Category_1: Total
               Category_2: Total
....

I am able to use annotate() to get a queryset via:

Purchase.objects.values('date__year', 'date__month','category')
   .filter(purchaser__username='testuser')
   .annotate(total=Sum('price')).order_by()

which gives me the total sum for each category, but each category is in a separate dict for the same year and month.

QuerySet [
{'date__year': 2020, 'date__month': 1, 'category': 'Category_1', 'total': 3000}, 
{'date__year': 2020, 'date__month': 1, 'category': 'Category_2', 'total': 3500}]

Is there anyway to create a summary in the format I want through a queryset, or am I going to have to build it manually based on the queryset I have shown?

Making it manually seems like it would require a lot of iteration of the queryset which wouldn’t be efficient so I’m trying to avoid that but I can’t find another way to do it.

Thanks!

2

Once your data is in-memory, repeat iteration over it is quite efficient. Python and computers are fast! The slow bits in database queries are normally networking and disk reads. See Latency Numbers Every Programmer Should Know.

As I understand, you want a dict of dicts of dicts. There’s no way to make a QuerySet return that, querysets only return lists.

I think you want something like (untested):

from collections import defaultdict

qs = Purchase.objects...
data = defaultdict(lambda: defaultdict(dict))
for year, month, category, total in qs:
    data[year][month][category] = total

Using defaultdict is a neat trick to shortcut creating all those dicts.

1 Like
3

Thanks Adam!

I was thinking that I would have to execute the query multiple times during iteration, but clearly I was wrong.

Is there a good way to display this type of dict of dicts of dicts in the template besides just iterating through it in the template?

I got that method working after looping back through the data dictionary and setting
.default_factory = None for each defaultdict. I’m trying to see if I can get the same result using regroup on the original queryset.