Django: Annotate queryset based on the existence of many-to-many relationships

I am using Django 5.2 with Postgres 17 and try to modify the queryset of a ListView for row-based access control. Items are to be included in the queryset either if the user is part of a project that is directly related with the item, or if the project that is related with the item is set to be "visible". The user is presented with different information and interaction possibilities in the front end depending which of the two options ("accessible" vs "visible") was true. The following solution in theory yields the right result:

def get_queryset(self):
    queryset = self.model.objects.all()
    if self.request.user.is_superuser:
        return queryset.annotate(accessible=Value(True))

    # Cache all projects that the user is part of. A project is a Django-group
    # (one-to-one relationship) with some additional attributes.
    user_projects = Project.objects.filter(group__in=self.request.user.groups.all())

    # Get all items that the user can access and mark them accordingly.
    accessible_items = (
        queryset
        .filter(groups__project__in=user_projects)
        .annotate(accessible=Value(True))
    )

    # Get all items that the user can see (but not access), and mark them accordingly.
    visible_items = (
        queryset
        .filter(groups__project__in=Project.objects.filter(visible=True))
        .exclude(groups__project__in=user_projects)
        .annotate(accessible=Value(False))
    )

    return accessible_items.union(visible_items)

The approach is simple enough and I'm not too concerned about efficiency, but there is a significant drawback. I'm using a union of two querysets, and as the Django docs state ...

only LIMIT, OFFSET, COUNT(*), ORDER BY, and specifying columns (i.e. slicing, count(), exists(), order_by(), and values()/values_list()) are allowed on the resulting QuerySet.

Because I'm using the django-filter package, a NotSupportedError is raised when the user tries to filter the list.

I've tried to replace the union with "|" in combination with distinct(), but that doesn't preserve the value of the annotated accessible-field. I've also looked into Subquery, Exists and OuterRef, but can't wrap my head around the concepts, because two many-to-many relationships are involved: A user can be part of multiple projects/groups and an item can be part of multiple projects/groups. Any help would be grately appreciated.

Вернуться на верх