Django find exact match for Many To Many relationship

Question

Lets say tat I have such a model:

class Conversation(models.Model):
    sid = models.CharField(
        max_length=34,
        primary_key=True
    )
    members = models.ManyToManyField(to=User)

I would like to find a Conversation that is connected exactly/only with users [1,2]

I tried this:

conversation = Conversation.objects.filter(
            members__in=[1, 2]
        )

But it returns all conversation that are connected with either user 1, or 2.

I tried this: other answer but It returns empty queryset.

score 4 · Answer 1 · edited Feb 04 '22 at 20:05

4

We can count how many members match the list, and check if that includes all the members:

from django.db.models import Count, Q

members = [1,2]
members_len = len(set(members))

Conversation.objects.annotate(
    total_members=Count('members'),
    matching_members=Count('members', filter=Q(members__in=members))
).filter(
    matching_members=members_len,
    total_members=members_len
)

This thus will retrieve Converstions that contain all members in the members list, and only these members (so not a superset, nor a subset).

Or for pre django-2.0 installations:

from django.db.models import Case, Count, When

members = [1,2]
members_len = len(set(members))

Conversation.objects.annotate(
    total_members=Count('members'),
    matching_members=Count(Case(
        When(members__in=members, then='members'),
        default=None
    ))
).filter(
    matching_members=members_len,
    total_members=members_len
)

edited Feb 04 '22 at 20:05

lueho

186
11

answered Sep 04 '19 at 14:12

Willem Van Onsem

443,496
30
428
555

1

total_members is repeated – Przemek Sep 04 '19 at 14:14
django.core.exceptions.FieldError: Cannot resolve keyword 'total_members' into field. Choices are: members, – Przemek Sep 04 '19 at 14:18
1

@Przemek: sorry, that should have been an `.annotate(..)` apparently there was a brainfreeze when writing this answer. – Willem Van Onsem Sep 04 '19 at 14:19
Should not it be total_members=Count('members') instead of total_members=Sum('members')? – Przemek Sep 04 '19 at 14:38
@Przemek: why would we `Sum(..)` members? Notice the `filter=Q(members__in=members)`, so we count the number of `members` in the list of members we are interesting in. – Willem Van Onsem Sep 04 '19 at 14:39
@Przemek: then you likely use a pre 2.0 Django version? – Willem Van Onsem Sep 04 '19 at 14:41
I use Django 1.11 – Przemek Sep 04 '19 at 14:42
@Przemek: then we will have to do the `Case`-`When` work ourseleves :s – Willem Van Onsem Sep 04 '19 at 14:46

Django find exact match for Many To Many relationship

1 Answers1