如何通过SQLAlchemy获取多对多关联表中用户角色及created_at字段

嘿，这个场景我太熟悉了！当你的多对多关联表带有额外字段（比如这里的created_at）时，SQLAlchemy默认的secondary关联方式就没法直接拿到这些字段了，得改用关联对象模式来处理。下面是具体的实现步骤：

1. 重构关联表为Model #

首先把原来的roles_users从普通的Table改成一个完整的Model，这样就能映射并访问created_at字段：

class RolesUsers(db.Model):
    __tablename__ = 'roles_users'
    # 复合主键
    user_id = db.Column(db.Integer(), db.ForeignKey('user.id'), primary_key=True)
    role_id = db.Column(db.Integer(), db.ForeignKey('role.id'), primary_key=True)
    created_at = db.Column(db.DateTime(), default=db.func.now())  # 可选：添加默认值自动生成时间

    # 建立与User、Role的双向关联
    user = db.relationship('User', back_populates='roles_assoc')
    role = db.relationship('Role', back_populates='users_assoc')

2. 修改User和Role模型的关联关系 #

把原来直接用secondary的关联，改成通过新的RolesUsers模型来关联：

class Role(db.Model, RoleMixin):
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(80), nullable=False, unique=True)
    # 替换原有的users关系，关联到RolesUsers
    users_assoc = db.relationship('RolesUsers', back_populates='role')

class User(db.Model, UserMixin):
    id = db.Column(db.Integer(), primary_key=True)
    email = db.Column(db.String(255), nullable=False, unique=True)
    # 替换原有的roles关系，关联到RolesUsers
    roles_assoc = db.relationship('RolesUsers', back_populates='user')

    # 可选：添加property保持原有的roles访问习惯
    @property
    def roles(self):
        return [assoc.role for assoc in self.roles_assoc]

3. 查询并构造目标格式的数据 #

现在可以直接查询RolesUsers表，关联User和Role来获取需要的所有字段：

from sqlalchemy import select

# 示例：获取user_id=1的所有角色关联信息
query = select(
    User.id.label('user_id'),
    Role.name.label('role'),
    RolesUsers.created_at
).join(RolesUsers).join(Role).where(User.id == 1)

# 执行查询并转换为目标字典格式
result = db.session.execute(query).all()
output = [
    {
        'user_id': row.user_id,
        'role': row.role,
        'created_at': row.created_at
    } for row in result
]

执行后就能得到你想要的格式：

[
    {'user_id':1, 'role':'user', 'created_at': datetime.datetime(2024, 5, 20, 10, 30)},
    {'user_id':1, 'role':'admin', 'created_at': datetime.datetime(2024, 5, 21, 9, 15)}
]

为什么要这么做？ #

SQLAlchemy的secondary参数只适用于无额外字段的纯关联表，当关联表有自己的业务字段（比如创建时间、权限等级等）时，必须将其定义为独立的Model，通过两个一对多关系来连接主表，这样才能直接访问到这些额外字段。

内容的提问来源于stack exchange，提问作者Šimon Kostolný