CODEWITHSUNDEEP
pythondjangopostgresqldjango-querysetdjango-aggregation
Bernd Wechner
Bernd Wechner

Reputation: 2133

Django query to get frequency-per-day of events with a DateTimeField

I have a model with events that each have a DateTimeField and can readily write a query that gets me the count of events per day:

MyEvents.objects.annotate(day=TruncDate('date_time')).values('day').annotate(count=Count('id'))

which produces (Postgresql) SQL like:

SELECT ("MyEvents"."date_time")::date AS "day",
       COUNT("MyEvents"."id") AS "count"
FROM "Doors_visit"
GROUP BY ("MyEvents"."date_time")::date

which produces a QuerySet that returns dicts containing 'date' and 'count'.

What I would like is to produce a histogram of such counts, by which I mean plotting the 'count' as a category along the x-axis and the number of times we've seen that 'count' on the y-axis.

In short, I want to aggregate the counts, or count the counts if you prefer.

Alas, Django complains vehemently when I try:

MyEvents.objects.annotate(day=TruncDate('date_time')).values('day').annotate(count=Count('id')).annotate(count_count=Count('count'))

produces:

django.core.exceptions.FieldError: Cannot compute Count('count'): 'count' is an aggregate

Fair enough. Which raises the question, can this be done in one Django Query without resorting to Raw SQL? I mean in SQL it's easy enough to query the results of a query (subquerying). Django seems to lack the smarts for this (I mean, rather than complain it could just wrap the query to date as a subquery and build a query from there, but it elects not to).

I mean, one work around is to extract the count of counts in Python from the result of the first query above. But it can be done in SQL, and arguably should be done in SQL, and I'm left wondering if in the forward march of Django's evolution and my learning there isn't an overlooked feature of Django or some sly trick that makes this possible which I have simply not uncovered.

Upvotes: 1

Views: 213

Answers (0)

Related Questions