I defined a role / permission model using the following model structure:
class Role(db.Model):
__tablename__ = 'roles'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(120), unique=True, nullable=False)
permissions = db.relationship("Permission", secondary=Role2Permission.__tablename__)
@classmethod
def find_by_id(cls, id):
return cls.query.filter_by(id=id).first()
class Role2Permission(db.Model):
__tablename__ = 'role_2_permission'
role_id = db.Column(db.Integer, db.ForeignKey('roles.id'), primary_key=True)
permission_id = db.Column(db.Integer, db.ForeignKey('permissions.id'), primary_key=True)
bool_value = db.Column(db.Boolean, default=False)
class Permission(db.Model):
__tablename__ = 'permissions'
id = db.Column(db.Integer, primary_key=True)
action = db.Column(db.String(255))
role2permission = db.relationship('Role2Permission', lazy='joined')
bool_value = association_proxy('role2permission', 'bool_value')
When I fetch a role I want to have the permission value (bool_value) to be set via the association proxy coming from the association table. However using cls.query.filter_by(id=id).first() (where cls is the Role) returns the wrong bool_values for the queried role. I think the reason can be seen when looking at the generated SQL:
SELECT permissions.id AS permissions_id,
permissions.action AS permissions_action,
role_2_permission_1.role_id AS role_2_permission_1_role_id,
role_2_permission_1.permission_id AS role_2_permission_1_permission_id,
role_2_permission_1.bool_value AS role_2_permission_1_bool_value
FROM role_2_permission,
permissions
LEFT OUTER JOIN role_2_permission AS role_2_permission_1 ON permissions.id = role_2_permission_1.permission_id
WHERE 1 = role_2_permission.role_id AND permissions.id = role_2_permission.permission_id
I think this is fetching too many rows because it's selecting from the permissions table instead of just joining it to the role_2_permission table but then for some reason joining role_2_permission again. Somehow flask / sqlalchemy is then reducing the returned rows in a bad way: It's not actually so instead of the bool_values that belong to e.g. role 1, it returns the bool_values that belong to role 2.
How do I have to fix my model to get the correct permission data when querying the role?