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')
)