Use custom join query with Django ManyToMany relationship

Using Django
1

Django question, concerning custom ManyToMany relationships: I have a User model which connects to a Role model via a ManyToMany, and then the Role model connects to two models, SitePermission or ForumPermission. Problem is, I can’t figure out how to setup that connection.

Here are my models, with (I believe) irrelevant fields removed.

Role:

    class Role():
        class RoleTypes(models.TextChoices):
            SITE = "s", "Site"
            FORUM = "f", "Forum"
    
        role_type = models.CharField(max_length=1, choices=RoleTypes.choices, null=True)
        permissions = models.ManyToManyField(
            "permissions.Permission",
            related_name="roles",
            through="permissions.RolePermissions",
        )

RolePermissions:

    class RolePermissions():
        role = models.ForeignKey("permissions.Role", on_delete=models.PROTECT)
        permission = models.ForeignKey("permissions.SitePermission", on_delete=models.PROTECT)

SitePermissions:

    class SitePermissions():
        permission = models.CharField(max_length=64)

ForumPermissions:

    class ForumPermissions():
        permission = models.CharField(max_length=64)
        forum = models.ForeignKey(
            "forums.forum", db_column="forumId", on_delete=models.PROTECT
        )

Problem of course, is that I can’t connect multiple tables to the ManyToMany. If I was doing it in SQL, I’d have something like roles r LEFT JOIN role_permissions rp_s ON r.type = 's' and r.id = rp.role_id INNER JOIN site_permissions sp ON rp_s.permission_id = sp.id with a second set of joins for forum_permissions (I know that SQL isn’t exactly right, more for example).

So is there a way to accomplish this? Conditionally link to multiple tables? Or do I need to have a separate property in Role for each SitePermission and ForumPermission, and then have logic for each to utilize it?

2

Your “ForumPermissions” class is named “SitePermissions” - is that a typo?

You don’t need to connect multiple tables through the ManyToMany. You can chain references through multiple FK links directly in your filter - Django knows how to build the joins from that.

For example, if you had a forum named “Django”, and you wanted to find every user having “read” access to Django, you can do something like:
User.objects.filter(role__rolepermissions__sitepermissions__permission="read", role__rolepermissions__sitepermissions__forum__forum_name="Django")

(I’m not sure I understand your structure here - between the what-appears-to-me naming issue with “SitePermissions” and what appears to be one or more missing FKs, I’m pretty sure that what I’ve posted is wrong - but it should give you some ideas for further investigation.)

2 Likes
3

Thanks for that catch Ken, it was purely a typo. I fixed it to ForumPermission.

So if I understand you correctly, ManyToMany is a convenience Django provides, but in my particular case, why bother? I can setup foreign keys on the join tables to the other models, and I can create a manager that does the logic for me. Then I just have some “repeat” logic on the other models that would otherwise be done by the ManyToMany.

Does that sound accurate to what you’re describing?

4

I’m not actually describing any specific recommendations regarding your data models - I don’t understand the requirements well enough to do so.

The only point I was trying to make is that the Django ORM can handle multiple-chained FK relationships, building the types of queries that it appears you are looking to build. You just need to “walk the chain” of the relationships and the ORM builds the joins based on the FK fields.

5

Fair enough, thanks!

Yah, my original idea was how to connect Role to either SitePermission or ForumPermission, depending on the value of type in Role. As I said, in regular SQL, it would be a custom JOIN, using the type as a join condition. A Role can either be for site content (accessSiteSection1, editSiteSection2) or be for a forum ((1, write), (5, edit)). I’d like to be able to access them off the same property, because permissions can only be either for the site or for the forum. I did consider if I should make it two separate properties and only call on the one that makes sense for the particular type, but I guess as a programmer, I’m lazy and would prefer the same key for any permission :stuck_out_tongue:

6

Such a thing is possible - I’m not sure I’d do it that way though. When I’m looking at establishing permission models, I always start with how those permissions need to be used and then work backward to the models that appear to best support those requirements.

But I always start with Django’s default structure of Group and Permission models to see if they’ll fit the requirements.