Django ORM Annotations: Most common group of user queryset

Question

I have a QuerySet of the auth.User model.

I want to know: Which groups are the ones which contain the 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 which are in the given User-Queryset):

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

How can I get an annotated QuerySet of the model Group this with Django 1.8?

Use Case

Show how many members with "x" in the user name 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

Group g4 has no matching members, but it should be in the 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())),
)