Django ORM Annotations: The Most Common User Query Group

Question

I have a QuerySet auth.User model.

I want to know: which groups are the ones that contain most of these users.

Example: I have three groups with these users:

• g1: u1 u2 u3 u4
• g2: u1 u2
• g3: u1 u4 u5 u6
• g4: u5 u6 u7

User-QuerySet: u1 u2 u3

Result (counting the members that are in this User-Queryset):

• g1: count 3
• g2: count 2
• g3: count 1
• g4: count 0

How can I get annotated QuerySet Group models using Django 1.8?

Use case

Show how many members with "x" in the username of each group has.

SQL

SELECT auth_group.id, auth_group.name, (SELECT COUNT(auth_user_groups.id) FROM auth_user_groups WHERE auth_user_groups.group_id = auth_group.id AND auth_user_groups.user_id IN (SELECT auth_user.id FROM auth_user WHERE username LIKE '%x%')) AS user_cnt FROM auth_group ORDER BY user_cnt DESC; 

Update

The g4 group does not have corresponding elements, but must be in an annotated QuerySet.

Answer 1

 from django.db.expressions import Case, When from django.db.models import Count, IntegerField Group.objects.annotate( user_cnt=Count(Case(When(user__in=user_list, then=1), output_field=IntegerField())), ) 

Answer 2

 list_group = [] for grp in Group.objects.filter(id__in = [g1, g2, g3, g4]): print grp, '---', User.objects.filter(id__in = [u1, u2, u3], groups = grp).count() list_group([grp, User.objects.filter(id__in = [u1, u2, u3], groups = grp).count()]) 

Hope this answer is acceptable.

Answer 3

In Django, we can group some object based on values , and we can apply annotation to the grouping result. Read this link , it will explain how to use values ​​and annotations.

 group = Group.objects.filter(id=XX).values("user").annotate(Count("user__pk")) 

If you only need users under the group, follow these steps:

 group = Group.objects.annotate(Count("user__pk"))