如何使用SQLAlchemy ORM查询1:多:多关系?

Question

代码

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()

总之，这个查询是超级…就我所知，在所有意义上都是坏的。UserToUserGroup和UserGroupToRole为数据透视表，定义为

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")))

其他三种型号都很标准。通过数据透视表UserToUserGroup,User与UserGroup具有1:many关系。通过UserGroupToRole数据透视表，UserGroup和Role之间存在1:many关系。

在这些模型上定义的所有关系都是lazy="joined"

问题:如何有效地将用户(通过提供的用户id)映射到所有相关的Roles?所有我想返回的是Role实例，对于给定的user_id

我认识到这将返回User和Role实例之间的所有数据(包括查询未指定的其他关系，因为即使那些是lazy=join)，这是不可取的。

Answer 1

使用连接应该简化查询，它也可能更有效，但我无法证明断言:

with Role.get_session() as session:
roles = session.query(Role)
.join(UserGroupToRole)
.join(UserGroup)
.join(UserToUserGroup)
.join(User)
.filter(User.user_id == user_id)
.all()