Problem with Count() and TruncDate

Using Django
1

Hi,

I’m currently using Python 3.9.6 and Django 3.2.5 with sqlite as database.

Given the following (simplified) model…

class printout(models.Model):
date_printed = models.DateTimeField(‘Printed on’, auto_now_add=True, blank=True, null=True)

…I am trying to find out how many printouts exist per day.

According to some examples from StackOverflow something like the following should do the magic
:slight_smile:

printout.objects.annotate(date=TruncDate(‘date_printed’)).values(‘date’).annotate(count=Count(‘date’))

But unfortunately, I always get a query set that contains every single record with a count of “1”

Any idea what I’m doing wrong?

2

You shouldn’t count date, and you can use the __date transform to simplify this a bit, e.g.

printout.objects.values('date_printed__date').annotate(count=Count())
3

Seems like Count() doesn’t like this…

missing 1 required positional argument: ‘expression’

I’m trying to get this working for most of the morning now and think I’ve been through many different variations of the query :slight_smile:

4

Sorry I missed an expression, you can use '*' or 'pk', e.g.

printout.objects.values('date_printed__date').annotate(count=Count('pk'))
5

nope… it simply does not work… this is the result query set…

{‘date_printed__date’: datetime.date(2021, 10, 13), ‘count’: 1},
{‘date_printed__date’: datetime.date(2021, 10, 14), ‘count’: 1},
{‘date_printed__date’: datetime.date(2021, 10, 14), ‘count’: 1},
{‘date_printed__date’: datetime.date(2021, 10, 14), ‘count’: 1},

What I would expect:

{‘date_printed__date’: datetime.date(2021, 10, 13), ‘count’: 1},
{‘date_printed__date’: datetime.date(2021, 10, 14), ‘count’: 3},

This is the SQL statement:

SELECT django_datetime_cast_date(“apotheken_printout”.“date_printed”, ‘Europe/Berlin’, ‘UTC’), COUNT(“apotheken_printout”.“id”) AS “count” FROM “apotheken_printout” INNER JOIN “apotheken_apotheken” ON (“apotheken_printout”.“apotheke_id” = “apotheken_apotheken”.“id”) WHERE “apotheken_printout”.“apotheke_id” = 11 GROUP BY django_datetime_cast_date(“apotheken_printout”.“date_printed”, ‘Europe/Berlin’, ‘UTC’), “apotheken_apotheken”.“name”, “apotheken_printout”.“date_printed”

6

Do you have a default ordering by name on your model?

Try clearing any ordering on your queryset using printout.order_by().value(...).annotate(...) see if that makes a difference.

(See Aggregation | Django documentation | Django for reference)

7

Hi Baptiste,

YES! That was the cause of the problem… now it works.

Thank you!

8

You’re welcome, I’ve ran into this exact issue more than a few times myself.

Checking the raw SQL was a good idea: it’s the “apotheken_apotheken”.“name” in the GROUP BY clause that gave it away for me (though it’s hard to spot in the middle of all that auto-generated goodness :slight_smile: )