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?

@Rakesh that is an error: `Cannot resolve keyword 'date' into field.` and if I change that to `created_at`, it gives nothing. — Jahongir Rahmonov, Jan 17 '18 at 12:51
Log.objects.annotate(date=TruncDate('created_at')).values('d‌ate').distinct().annotate(c=Count(‌'id'))? — Rakesh, Jan 17 '18 at 12:52

score 1 · Accepted Answer · answered Jan 17 '18 at 12:49

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'))

answered Jan 17 '18 at 12:49

Dima Kudosh

7,126
4
36
46

That's exactly it. I looked at the underlying SQL query and it was adding an additional field to `GROUP BY`. Thank you. – Jahongir Rahmonov Jan 17 '18 at 13:49

score 0 · Answer 2 · answered Jan 17 '18 at 13:07

0

You can use the distinct() method to get unique values in Django

Ex:

Log.objects.annotate(date=TruncDate('created_at')).values('d‌‌ate').distinct()

answered Jan 17 '18 at 13:07

Rakesh

81,458
17
76
113

Group By each date in Django 2

2 Answers2