1

I am trying to model a very typical permission structure where a User model (the human user of my website) can be assigned to some Roles, and each of those Roles has some Permissions.

It would be really helpful if I could get a relationship from User straight to Permission. That way, I could fetch a user (instance) from the database and just do user.permissions to get his permissions, put some filtering to check if a user has a specific Permission, have them pre-loaded... in sum: all the goodies that come with a relationship.

A viewonly relationship would be perfectly fine. As mentioned by Mike Bayer in a very similar question, I can't be writing to User.permissions because we don't not know which 'Role' to use or where to insert it.

I have created two intermediate tables:

User -- M2M --> Role(s) -- M2M --> Permission(s)
 |                                     ^
 +-------- user.permissions -----------+
  • users_roles to connect the user to its roles through their primary key (the ID)
  • and roles_permissions to connect each role to its permissions,

This is my table structure (simplified for the question, but even the full version is really typical and... simple)

class User(DeclarativeBase, Mixin):
    __tablename__ = 'users'
    email = Column(String(25), nullable=False)

_users_roles = Table('users_roles', DeclarativeBase.metadata,
    Column('user_id', ForeignKey('users.id', ...
    Column('role_id', ForeignKey('roles.id', ...
    PrimaryKeyConstraint('user_id', 'role_id',),
)    

class Role(DeclarativeBase, Mixin):
    __tablename__ = 'roles'

    name = Column(Text(), nullable=False, unique=True)
    users = relationship("User", secondary=_users_roles, backref="roles")

_roles_permissions = Table('roles_permissions', DeclarativeBase.metadata,
    Column('role_id', ForeignKey('roles.id',  ...
    Column('permission_id', ForeignKey('permissions.id', ...
    PrimaryKeyConstraint('role_id', 'permission_id',),
)

class Permission(DeclarativeBase, Mixin):
    __tablename__ = 'permissions'

    key = Column(Text, nullable=False, unique=True,)

I saw this other answer that looks very promising, yet I don't seem to be able to make it work. Honestly, I've been trying a lot of combinations and I'd say that the furthest I got was with this:

permissions = relationship('Permission',
    secondary="""join(User, users_roles, 
                     User.id == users_roles.c.user_id
                 ).join(roles_permissions, 
                    users_roles.c.role_id == roles_permissions.c.role_id
                ).join(
                  Permission, roles_permissions.c.permission_id == Permission.id
               )""",
    viewonly=True,
)

Which gives me this error:

sqlalchemy.exc.ArgumentError: Relationship User.permissions 
could not determine any unambiguous local/remote column pairs 
based on join condition and remote_side arguments.  Consider 
using the remote() annotation to accurately mark those elements
of the join condition that are on the remote side of the relationship.

Any hints would be greatly appreciated. Thank you in advance.

Savir
  • 17,568
  • 15
  • 82
  • 136

1 Answers1

4

The secondary should not include the related tables themselves, but just the association between them:

permissions = relationship(
    'Permission',
    secondary="""join(users_roles, roles_permissions, 
                      users_roles.c.role_id == roles_permissions.c.role_id)""",
    viewonly=True,
)

Having the to-be-joined tables in the secondary confuses the automation that is trying to find the foreign key relationships between User and Permission, through the secondary.

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
  • OMG... Hours!!... I spent hours trying different combinations of _things_!! Thank you very much. – Savir May 06 '19 at 11:30
  • Is the concept here that the join effectively creates a single association table? – SuperShoot May 07 '19 at 02:22
  • 2
    @SuperShoot Pretty much. You can use about any table (that has the foreign keys present) as the secondary, be it a result of a join, select, or some such: https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#composite-secondary-joins. Due to how SQLA handles deduplicating entities, it is not a problem even if the join produces the same permission id multiple times. – Ilja Everilä May 07 '19 at 05:51
  • _it is not a problem even if the join produces the same permission id multiple times._ I can assess for this. I created a test where a `user` belongs to two different roles, each of them with the same permission, and in my `user.permissions` relationship I only get one `Permission` instance even if I don't specify `collection_class = set` in the `relationship`. This is something that worried me a little (getting duplicate permissions) but no: it doesn't happen... It's working great :+1: – Savir May 07 '19 at 11:25