CODEWITHSUNDEEP
sqldjangodjango-modelsdjango-orm
David Downes
David Downes

Reputation: 1205

Django ORM Left Join onto same table

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?

Upvotes: 2

Views: 276

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476709

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

Upvotes: 1

Related Questions