django Create SQL query via ORM with implicit links, is it possible?

Question

Django

Good day

everyone I'm creating a forum with pybb, I need to add additional permissions for each individual forum.
Decided to use django_guardian for this. There was a difficulty with the fact that I can’t imagine how you can create smart sql queries through ORM without having a Foreign key between tables.

There are following tables in the database:


In django they are represented as follows:

# django auth permission model:
class Permission(models.Model):
    name = models.CharField(_('name'), max_length=50)
    content_type = models.ForeignKey(ContentType)
    codename = models.CharField(_('codename'), max_length=100)

# guardian group permission model
class GroupObjectPermissionBase(BaseObjectPermission):
    permission = models.ForeignKey(Permission)
    content_type = models.ForeignKey(ContentType)
    object_pk = models.CharField(_('object ID'), max_length=255)
    content_object = GenericForeignKey(fk_field='object_pk')
    group = models.ForeignKey(Group) # reference to django group

class Forum(models.Model):
    name = models.CharField(_('Name'), max_length=80, default='')
    slug = models.SlugField(default='', unique=True)
    hidden = models.BooleanField(_('Hidden'), blank=False, null=False, default=False)
    headline = models.TextField(_('Headline'), blank=True, null=True)

    class Meta(object):
        permissions = (
           ('view_forum', 'Only view forum'),
    )

Those at the SQL level, the Forum and GroupObjectPermission tables are not connected in any way (moreover, their "for communication" fields even have a different data type)

I need to get all the forums for which the group # 1,2,3 has an entry in the GroupObjectPermission table with a link to Permission and the code word 'view_forum'.

Or in other words - In the GroupObjectPermission table with group_id = (1,2,3), find a connection with the Permission table where codename = 'view_forum' and something else GroupObjectPermission.object_id = Forum.pk

In the form of SQL (otherwise everything is very confusing in words ):

SELECT * FROM pybb_forum as ff 
    JOIN guardian_groupobjectpermission as gg ON ff.id = gg.object_pk 
    JOIN auth_permission as pp ON gg.permission_id = pp.id 
    WHERE codename = 'view_forum' 
    AND gg.group_id in (1,2,3)
    AND gg.content_type_id = 9

I would like to write something like:
Forum.objects.filter(groupobjectpermission__permission__codename='view_forum') but unfortunately django doesn't know how to link Forum and GroupObjectPeermission.
Is it possible to write such a request on django-orm? Through extra?

Thanks in advance for your advice

Answer 1

While writing a question in Q&A, I figured out the ORM.
Specifically, in my case, the query can be rewritten from JOIN to WHERE:

SELECT * FROM pybb_forum as ff,guardian_groupobjectpermission as gg, auth_permission as pp 
     WHERE codename = 'view_forum'      
     AND gg.group_id in (1,2,3)     
     AND gg.content_type_id = 9 
     AND  ff.id = gg.object_pk 
     AND gg.permission_id = pp.id;

a WHERE is already quite easy to substitute in objects.extra:

Forum.objects.extra(
    tables = ['guardian_groupobjectpermission', 'auth_permission'],
    where = [""" codename = "view_forum"
    AND guardian_groupobjectpermission.group_id in (1,2,3)
    AND guardian_groupobjectpermission.content_type_id = 9
    AND pybb_forum.id = guardian_groupobjectpermission.object_pk
    AND guardian_groupobjectpermission.permission_id = auth_permission.id
    """]
)

The only problem is that the readability of such a request is completely gone.

Answer 2

You were also told above that it's all there in the dock. Or do you need to be done for you? Well, for the first time I did it for you, but you should be ashamed. Add to the Forum model:

groupobjectpermission = GenericRelation(GroupObjectPermissionBase, 
                                 content_type_field='content_type',
                                 object_id_field='object_pk')

And then it will work as you wanted -Forum.objects.filter(groupobjectpermission__permission__codename='view_forum')