I have a custom permissions system in a django project that can link any user to any specific model instance to grant permission on that object. It is more complex than this, but boiled down:
class Permission(models.Model): user = models.ForeignKey(User) content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE) object_id = models.PositiveIntegerField() content_object = GenericForeignKey('content_type', 'object_id')
I want to query for all permissions for any user, that are linked to the same content_object(s) that a particular user has been granted permission(s) on. Phrased differently, the user in question wants to see who else has permissions on any of the same objects that they do.
In SQL, the following does the trick:
SELECT perm1.* FROM app_permission perm1 LEFT JOIN app_permission perm2 ON perm2.user_id = 12345 AND perm1.content_type_id = perm2.content_type_id AND perm1.object_id = perm2.object_id WHERE perm2.id IS NOT NULL;
Is it possible to achieve this in the django ORM?
You can work with an
Exists subquery [Django-doc] which is likely what you intend to do, so:
from django.db.models import Exists, OuterRef Permission.objects.filter( Exists(Permission.objects.filter( user_id=12345, content_type_id=OuterRef('content_type_id'), object_id=OuterRef('object_id') )) )
another option could be to use a “one-two trick” with the
ContentType model and work with:
from django.db.models import F Permission.objects.filter( content_type__permission__user=12345, content_type__permission__id__isnull=False, content_type__permission__object_id=F('object_id') )