SqlAlchemy returns wrong data for association table with association proxy

Viewed 253

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?

0 Answers
Related