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.