Group By each date in Django 2

Question

I have a model called Log which has a datetime field created_at.

What I want to do is to calculate the number of Logs for each date.

I tried to do this:

from django.db.models.functions import TruncDate

Log.objects.annotate(date=TruncDate('created_at')).values('date').annotate(c=Count('id'))

This is giving me the following:

{'date': datetime.date(2018, 1, 17), 'count': 1}, {'date': datetime.date(2018, 1, 17), 'count': 1}, {'date': datetime.date(2018, 1, 17), 'count': 2}

That is, the date is not unique.

The result I want would be this:

{'date': datetime.date(2018, 1, 17), 'count': 4}, {'date': datetime.date(2018, 1, 18), 'count': 2}

How could approach this problem?

Answer 1

If you set default ordering in your Log model extra field will be added to GROUP BY section of your query, and it can cause this problem. Try to remove ordering.

Log.objects.order_by().annotate(date=TruncDate('created_at')).values('date').annotate(c=Count('id'))