0

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)
Gorgine
  • 310
  • 2
  • 11
  • I have got the answer, ordering did the trick.https://stackoverflow.com/questions/43132917/in-django-orm-values-and-annotate-are-not-working-to-group-by – Gorgine Jan 24 '21 at 13:18
  • Even though the answer might be correct, consider not using `order_by` in your production apps as it is a huge performance hit – SLDem Jan 24 '21 at 13:31
  • Thx. But I have to do a order by if I do not change `order_by` on model meta. What about order_by() which do not specify any field to order? – Gorgine Jan 24 '21 at 13:44
  • Any ordering operations results in many database hits so your best option is either keeping the original order or reversing it using pythons `.reverse()` method – SLDem Jan 24 '21 at 15:20

0 Answers0