Query an exact match on a related model

I have the following Django models

class User(models.Model):
    name = models.CharField(max_length=10)

    def __str__(self):
        return f'User {self.id} - {self.name}'


class Chat(models.Model):
    users = models.ManyToManyField(User, related_name='chats')
    title = models.CharField(max_length=10)

The Chat model has a many-to-many relationship to the User model.

How can I find all the Chats for a specific group of users? Let's say we have the following Users

User 1 - Adam
User 2 - Bob
User 3 - Carl
User 4 - Dave

and they have the following Chats

Chat 1 - AB1 - Adam, Bob
Chat 2 - AB2 - Adam, Bob
Chat 3 - AC1 - Adam, Carl
Chat 4 - AC2 - Adam, Carl
Chat 5 - AD1 - Adam, Dave
Chat 6 - AD2 - Adam, Dave
Chat 7 - BC1 - Bob, Carl
Chat 8 - BC2 - Bob, Carl
Chat 9 - BD1 - Bob, Dave
Chat 10 - BD2 - Bob, Dave
Chat 11 - CD1 - Carl, Dave
Chat 12 - CD2 - Carl, Dave
Chat 13 - ABC1 - Adam, Bob, Carl
Chat 14 - ABC2 - Adam, Bob, Carl
Chat 15 - ABD1 - Adam, Bob, Dave
Chat 16 - ABD2 - Adam, Bob, Dave
Chat 17 - ACD1 - Adam, Carl, Dave
Chat 18 - ACD2 - Adam, Carl, Dave
Chat 19 - BCD1 - Bob, Carl, Dave
Chat 20 - BCD2 - Bob, Carl, Dave
Chat 21 - ABCD1 - Adam, Bob, Carl, Dave
Chat 22 - ABCD2 - Adam, Bob, Carl, Dave

And let's say we want to get all the Chats between User 1 - Adam and User 2 - Bob.

1. This

    for c in Chat.objects.prefetch_related('users').filter(users__in=[1, 2]):
        print(f'Chat {c.id} - {", ".join([u.name for u in c.users.all()])}')

will result into the following SQL queries

SELECT "app_chat"."id", "app_chat"."title" FROM "app_chat" INNER JOIN "app_chat_users" ON ("app_chat"."id" = "app_chat_users"."chat_id") WHERE "app_chat_users"."user_id" IN (1, 2)

SELECT ("app_chat_users"."chat_id") AS "_prefetch_related_val_chat_id", "app_user"."id", "app_user"."name" FROM "app_user" INNER JOIN "app_chat_users" ON ("app_user"."id" = "app_chat_users"."user_id") WHERE "app_chat_users"."chat_id" IN (1, 2, 3, 4, 5, 6, 13, 14, 15, 16, 17, 18, 21, 22, 7, 8, 9, 10, 19, 20)

and the result is all the Chats in which either User 1 - Adam or User 2 - Bob is (or both are) involved (with duplicates)

2. This (and Q)

    q = Q(users=1) & Q(users=2)
    for c in Chat.objects.annotate(user_count=Count('users')).prefetch_related('users').filter(q, user_count=2):
        print(f'Chat {c.id} - {", ".join([u.name for u in c.users.all()])}')

will result into the following SQL query

SELECT "app_chat"."id", "app_chat"."title", COUNT("app_chat_users"."user_id") AS "user_count" FROM "app_chat" LEFT OUTER JOIN "app_chat_users" ON ("app_chat"."id" = "app_chat_users"."chat_id") INNER JOIN "app_chat_users" T4 ON ("app_chat"."id" = T4."chat_id") WHERE (T4."user_id" = 1 AND T4."user_id" = 2) GROUP BY "app_chat"."id", "app_chat"."title" HAVING COUNT("app_chat_users"."user_id") = 2

and the result is simply empty

3. This (or Q)

    q = Q(users=1) | Q(users=2)
    for c in Chat.objects.annotate(user_count=Count('users')).prefetch_related('users').filter(q, user_count=2):
        print(f'Chat {c.id} - {", ".join([u.name for u in c.users.all()])}')

will result into the following SQL queries

SELECT "app_chat"."id", "app_chat"."title", COUNT("app_chat_users"."user_id") AS "user_count" FROM "app_chat" LEFT OUTER JOIN "app_chat_users" ON ("app_chat"."id" = "app_chat_users"."chat_id") INNER JOIN "app_chat_users" T4 ON ("app_chat"."id" = T4."chat_id") WHERE (T4."user_id" = 1 OR T4."user_id" = 2) GROUP BY "app_chat"."id", "app_chat"."title" HAVING COUNT("app_chat_users"."user_id") = 2

SELECT ("app_chat_users"."chat_id") AS "_prefetch_related_val_chat_id", "app_user"."id", "app_user"."name" FROM "app_user" INNER JOIN "app_chat_users" ON ("app_user"."id" = "app_chat_users"."user_id") WHERE "app_chat_users"."chat_id" IN (3, 4, 5, 6, 7, 8, 9, 10)
end current

and the result is all the Chats where either User 1 - Adam or User 2 - Bob is (but not both are) involved (without duplicates). This surprised me a bit as I would have expected that everything from Chat 1 to Chat 10 should be returned

4. This (and of 3 querysets)

    q1 = Chat.objects.prefetch_related('users').filter(users=1)
    q2 = Chat.objects.prefetch_related('users').filter(users=2)
    q3 = Chat.objects.annotate(user_count=Count('users')).filter(user_count=2)
    for c in q3 & q1 & q2:
        print(f'Chat {c.id} - {", ".join([u.name for u in c.users.all()])}')

will finally return the correct result, but I am wondering whether there is a better (more django-ish) way to achieve this

You can filter with:

<pre><code>from django.db.models import Count, Q

Chat.objects.alias(
user_count=Count('users'), match=Count('users', filter=<b>Q(users__in=[1, 2]</b>))
).filter(user_count=2, match=2)</code></pre>

This will ensure that there are two users involved in the chat, and that these two users are user 1 and user 2.