How to return exact matches for ManyToMany queries

user_1746422 0
Viewed 196

let's say I have a model like

class Group:
    users = ManyToMany(User, related_name='groups')

and I have groups with a different mixture of users

group1 = Group(users=[user1])
group2 = Group(users=[user1, user2])
group3 = Group(users=[user1, user2, user3])

How would I go about querying for groups only with user1 and user2?

I'd like to do something that's like Group.objects.filter(users=[user1, user2]) -> [group2]

but I'm getting TypeError: Field 'id' expected a number but got [<User: user1>, <User: user2>].

if I do Group.objects.filter(user__in=[user1, user2].distinct('id')

it returns back all the groups [group1, group2, group3]

I know how to go through the User instance to get all of the user's groups but I specifically want to go through the Group model.

I imagine I may have to do some type of matching with the ORM... maybe a subquery?

orm django python
1 Answers

You can filter with:

from django.db.models import Count, Q

# since Django-2.0

users = [user1, user2, user3]
users = set(users)

group1 = Group.objects.annotate(
    nusers=Count('users'),
    nuser_match=Count('users', filter=Q(users__in=users))
).filter(
    nusers=len(users),
    nusers_match=len(users)
)

This will thus count the total number of related users, as well as the number of users that match. Both these numbers should equal the number of users in the set.

user_675790
Related