Issue when counting a model extending auth_user by month

[anderleich]

I've been debugging for I while but I can't get the right result. In my django application I have extended the auth_user to add more fields:

from django.contrib.auth.models import User
class Person(User):
    api_id = models.CharField(max_length=255)
    api_key = models.CharField(max_length=255)

Now, I want to count persons by year and month according to the date they joined (date_joined from auth_user):

Person.objects.annotate(month=TruncMonth("date_joined")).values("month").annotate(count=Count("pk"))

However, all I get is a count=1 for each person in the database without grouping them by month.

This is the actual query being executed:

SELECT CAST(DATE_FORMAT(CONVERT_TZ(`auth_user`.`date_joined`, 'UTC', 'Europe/Madrid'), '%Y-%m-01 00:00:00') AS DATETIME) AS `month`, COUNT(`mt_api_app_person`.`user_ptr_id`) AS `count` FROM `mt_api_app_person` INNER JOIN `auth_user` ON (`mt_api_app_person`.`user_ptr_id` = `auth_user`.`id`) GROUP BY CAST(DATE_FORMAT(CONVERT_TZ(`auth_user`.`date_joined`, 'UTC', 'Europe/Madrid'), '%Y-%m-01 00:00:00') AS DATETIME), `auth_user`.`date_joined` ORDER BY `auth_user`.`date_joined` DESC

All count=1 seem to occur because of the GROUP BY [...] 'auth_user'.'date_joined' which is grouping the entries by the whole datetime and not just month and year.

Any clues of what's happening here? If I do the same query but with the User I get what I want.

SELECT CAST(DATE_FORMAT(CONVERT_TZ(`auth_user`.`date_joined`, 'UTC', 'Europe/Madrid'), '%Y-%m-01 00:00:00') AS DATETIME) AS `month`, COUNT(`auth_user`.`id`) AS `count` FROM `auth_user` GROUP BY CAST(DATE_FORMAT(CONVERT_TZ(`auth_user`.`date_joined`, 'UTC', 'Europe/Madrid'), '%Y-%m-01 00:00:00') AS DATETIME) ORDER BY NULL

[Carlton Gibson]

I think you need to count=Count("month"), rather than the PK. (​See the aggregation topic guide.)

Please don't use the issue tracker as a secondary support channel. See TicketClosingReasons/UseSupportChannels

[Carlton Gibson]

My comment is too quick. ​The use of `values` means the `pk` should work too..

However, it looks like you've hit the ​default `order_by` issue below the linked section there. (Note the ORDER BY auth_user.date_joined DESC in the SQL.) If you append .order_by() clearing the ordering it should work (as it is testing.)

Hopefully that helps.

[anderleich]

Thanks!! It definitely works, that was the issue. I had a default order_by specified in the Person model.

Replying to Carlton Gibson:

My comment is too quick. ​The use of `values` means the `pk` should work too..

However, it looks like you've hit the ​default `order_by` issue below the linked section there. (Note the ORDER BY auth_user.date_joined DESC in the SQL.) If you append .order_by() clearing the ordering it should work (as it is testing.)

Hopefully that helps.