How best to query a 1:many:many relationship efficiently using the SQLAlchemy ORM?

Question

Here's the code

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

In short, this query is super .. bad, in all senses as far as I can tell. UserToUserGroup and UserGroupToRole are pivot tables, and are defined as

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

The other three models are pretty standard. User has a 1:many relationship to UserGroup via the pivot table UserToUserGroup. UserGroup has a 1:many relationship to Role via UserGroupToRole pivot table.

All relationships defined on these models are lazy="joined"

Question: How do I map a user (by supplied user id) to all associated Roles, efficiently? All I want to return are Role instances, for a given user_id

I recognize that this will return all data between User and Role instances (including other relationships not specified by the query, since even those are lazy=joined), which isn't desirable.

Answer 1

Using joins should simplify the query, it might also be more efficient but I cannot prove that assertion:

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