django - Why my annotation group by is not working as expected?

Question

I wanna do a query like raw sql select count(b.status) from a left out join b on a.id = b.user_id group_by(b.status). I know how to get what I wanted using raw sql. But when I try to implement this using orm, it failed. Below is the code:

query_kwargs_dict = {'id__in': [10000, 10001]}
statistics = UserAuthModel.objects.filter(**query_kwargs_dict).select_related(
    'obbyuserinfomodel'
).values('obbyuserinfomodel__status').annotate(count=Count('obbyuserinfomodel__status')).query
print('statistics', statistics)

And the print statement output is:

statistics SELECT `users_obby_info`.`status`, COUNT(`users_obby_info`.`status`) AS `count` FROM `users_auth` LEFT OUTER JOIN `users_obby_info` ON (`users_auth`.`id` = `users_obby_info`.`user_id`) WHERE `users_auth`.`id` IN (10000, 10001) GROUP BY `users_auth`.`id`, `users_obby_info`.`status` ORDER BY `users_auth`.`id` DESC

What confusing me is the group_by. It should be grouped by the field specified in values. In my case, it is obbyuserinfomodel__status , but the output sql statement showed it was grouped by users_auth.id and users_obby_info.status together. What I want is just users_obby_info.status.

Below are the two tables(I have omitted some fields for simplicity):

class UserAuthModel(AbstractBaseUser):
    ...

class ObbyUserInfoModel(models.Model):
    user = models.OneToOneField(UserAuthModel, on_delete=models.CASCADE)
    ...
    status = models.CharField(max_length=15, default=ACCOUNTSTATUS.UNACTIVATED.value, null=True)

question from:https://stackoverflow.com/questions/65870571/why-my-annotation-group-by-is-not-working-as-expected

Answer 1

Waitting for answers