Django filter where every item in an object's many to many list is included in provided queryset

Question

I have the following models.

class Document(models.Model):
  allowed_groups = models.ManyToManyField(Group, related_name='allowed_documents')

class Person(models.Model):
  permission_groups = models.ManyToManyField(Group, related_name='people')

class Group(models.Model):
  id = models.BigIntegerField()

I want to find all the documents that Person can access with the condition that they have to be a member of all of the allowed groups.

I want this: Case

1. Document(allowed_groups=1,2,7) with Person(permission_groups=1,2,6,7,11,15) -> MATCH
2. Document(allowed_groups=1,2,7) with Person(permission_groups=1,7) -> NO_MATCH
3. Document(allowed_groups=1,2,7) with Person(permission_groups=1,2) -> NO_MATCH
4. Document(allowed_groups=1,2,7) with Person(permission_groups=2) -> NO_MATCH
5. Document(allowed_groups=1,2,7) with Person(permission_groups=8) -> NO_MATCH
6. Document(allowed_groups=1,2,7) with Person(permission_groups=1,2,7) -> MATCH

If I do this:

person = Person.objects.get(pk=1)
Document.objects.filter(allowed_groups__in=person.permission_groups.all())

I would match on all of the above cases except 8 (not what I want)

There are many questions on stack overflow that are asking about exact matching i.e. match only on case 6 but not case 1. (also not what I want)

So my question is how can I use django to do this? I've considered using SQL but surely there is a way to do with Django ORM. It doesn't seem like that much of a crazy requirement.

Note: I have a few other conditions (other types of groups and Document access levels) which I'ved turned into a complicated expression with chained filter/Q Objects, but I've got all that worked out except for this bit.

Also: I'm having a bit of trouble phrasing my question title, which might be why I can't find an answer. It doesn't need to be query set, it could just be a list of pks or some other method.

Answer 1

Based on this answer

Solution:

from django.db.models import Count, Q

person = Person.objects.get(pk=1)
permission_groups = set(person.permission_groups.all())
Document.objects.annotate(
    allowed_groups_count=Count('allowed_groups', filter=Q(allowed_groups__in=permission_groups))
).filter(
    allowed_groups_count__gt=0
)

And then its query will be something like this:

SELECT
  document.id,
  COUNT(document_allowed_groups.group_id) FILTER (
    WHERE
      document_allowed_groups.group_id IN (1, 2, 6, 7, 11, 15)
  ) AS allowed_groups_count
FROM
  document
  LEFT OUTER JOIN document_allowed_groups ON (
    document.id = document_allowed_groups.document_id
  )
GROUP BY
  document.id
HAVING
  COUNT(document_allowed_groups.group_id) FILTER (
    WHERE
      (
        document_allowed_groups.group_id IN (1, 2, 6, 7, 11, 15)
      )
  ) > 0