SQLAlchemy跨多关系查询：根据课程ID获取有权限用户

你的核心需求是绕过用户与课程的教师关联，仅通过Carnet权限关联来查询指定课程的有权限用户。以下是几种可行的ORM查询方案：

方案1：利用已定义的关系链显式关联 #

通过明确指定User→Carnet→Lesson的关联路径，避免SQLAlchemy自动使用User.teaches的默认关联：

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

target_lesson_id = 1  # 替换为你要查询的课程ID

users_with_access = session.query(User).join(
    Carnet, User.carnets  # 显式使用User与Carnet的关联
).join(
    Lesson, Carnet.lessons  # 显式使用Carnet与Lesson的关联
).filter(Lesson.id == target_lesson_id).distinct().all()

添加distinct()是为了避免同一用户因多个Carnet关联到同一课程而重复出现。

方案2：从目标课程反向遍历关联（最简洁） #

利用ORM已定义的关系直接访问，无需手动拼接join：

target_lesson = session.query(Lesson).get(target_lesson_id)
users_with_access = [carnet.user for carnet in target_lesson.carnets]

这种方式适合不需要额外过滤条件的场景，代码更直观。

方案3：显式指定外键条件（完全控制关联逻辑） #

直接用外键字段定义join条件，彻底避免ORM自动选择关联关系：

users_with_access = session.query(User).join(
    Carnet, User.id == Carnet.user_id
).join(
    CarnetLesson, Carnet.id == CarnetLesson.carnet_id
).join(
    Lesson, Lesson.id == CarnetLesson.lesson_id
).filter(Lesson.id == target_lesson_id).distinct().all()

为什么之前的join会出错？ #

当你直接执行session.query(User).join(Lesson)时，SQLAlchemy会自动匹配User和Lesson之间的第一个关联关系（即User.teaches与Lesson.teacher），导致查询到的是课程教师而非有权限用户。通过显式指定关联对象或外键条件，就能引导ORM走正确的权限关联路径。

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