如何通过SQLAlchemy ORM高效查询指定用户关联的所有Role实例？

现有问题代码 #

当前查询会返回多表关联的全部冗余数据，不符合仅返回Role实例的需求：

with Role.get_session() as session:
    query = session.query(User, UserGroup, UserToUserGroup, UserGroupToRole, Role)\
        .filter(User.user_id == UserToUserGroup.columns.user_id)\
        .filter(UserToUserGroup.columns.user_group_id == UserGroup.user_group_id)\
        .filter(UserToUserGroup.columns.user_group_id == UserGroupToRole.columns.user_group_id)\
        .filter(UserGroupToRole.columns.role_id == Role.role_id)\
        .filter(User.user_id == user_id)

    current_app.logger.error(str(query))
    res = query.all()

中间表定义 #

UserGroupToRole = Table("UserGroupsToRoles", base.metadata,
                        Column("user_group_id", INTEGER(unsigned=True), ForeignKey("UserGroups.user_group_id")),
                        Column("role_id", INTEGER(unsigned=True), ForeignKey("Roles.role_id")))


UserToUserGroup = Table("UsersToUserGroups", base.metadata,
                        Column("user_id", INTEGER(unsigned=True), ForeignKey("Users.user_id")),
                        Column("user_group_id", INTEGER(unsigned=True), ForeignKey("UserGroups.user_group_id")))

模型关联说明 #

• User 通过 UserToUserGroup 与 UserGroup 为1:多关联
• UserGroup 通过 UserGroupToRole 与 Role 为1:多关联
• 所有关联均设置 lazy="joined"

解决方案 #

方法一：利用模型关联链查询 #

借助已定义的ORM关联，代码更简洁，符合ORM使用习惯：

with Role.get_session() as session:
    # 获取目标用户（lazy=joined会自动加载关联的user_groups和roles）
    user = session.query(User).filter(User.user_id == user_id).first()
    if not user:
        return []
    
    # 遍历用户所属组，收集所有角色
    roles = []
    for user_group in user.user_groups:
        roles.extend(user_group.roles)
    
    # 去重（避免同一角色被多个组重复关联）
    unique_roles = list({role.role_id: role for role in roles}.values())

方法二：直接查询Role表（性能更优） #

手动关联必要表，仅查询Role数据，减少冗余传输，适合仅需Role实例的场景：

with Role.get_session() as session:
    roles = session.query(Role)\
        .join(UserGroupToRole, Role.role_id == UserGroupToRole.columns.role_id)\
        .join(UserGroup, UserGroup.user_group_id == UserGroupToRole.columns.user_group_id)\
        .join(UserToUserGroup, UserGroup.user_group_id == UserToUserGroup.columns.user_group_id)\
        .join(User, User.user_id == UserToUserGroup.columns.user_id)\
        .filter(User.user_id == user_id)\
        .distinct()\
        .all()

说明：distinct() 用于避免同一用户因所属多个组关联同一角色而返回重复实例。

内容的提问来源于stack exchange，提问作者geudrik