如何使用协会表，in子句和子查询查询sqlalchemy？

Question

我有一个课程表和一张学生表定义为

class Course(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200))
    tutor = db.Column(db.Integer, db.ForeignKey("tutor.id"))
    students = db.relationship("Student", secondary=association_table)


class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    email = db.Column(db.String(50))
    courses = db.relationship("Course", secondary=association_table)

与他们之间的许多关系，我创建了一个相关的表，

association_table = db.Table(
    "association",
    db.Model.metadata,
    db.Column("course_id", db.ForeignKey("course.id")),
    db.Column("student_id", db.ForeignKey("student.id")))

我想查询并获得特定学生所学的所有课程的结果。 SQL查询就像

SELECT * FROM course WHERE course.id IN (SELECT course_id FROM association WHERE student_id = 2)

我该如何使用SQLalchemy这样做？

目前，我的众多失败尝试的尝试之一是

query_ = self.session.query(Course)
query2 = self.session.query(association_table.course_id).all().filter_by(student_id=view_kwargs.get('student_id'))
query_ = query_.filter(Course.id.in_(query2)).all()

，当我使用Postman的API调用它时，我会得到这个。

AttributeError: 'Table' object has no attribute 'course_id'

Answer 1

带有子选项的子句中的不是最优雅的（至少在过去，也不是执行查询的最佳方法）。

为了使用 JOIN 具有绝对相同结果的子句，请尝试下面的查询，这很简洁：

q = session.query(Course).filter(Course.students.any(Student.id == 2))

并将产生以下 sql ：

SELECT course.id,
       course.name
FROM course
WHERE EXISTS
    (SELECT 1
     FROM association,
          student
     WHERE course.id = association.course_id
       AND student.id = association.student_id
       AND student.id = ?)

但是，避免在 student 表格，以下更明确的查询将产生相同的结果：

q = (
    session
    .query(Course)
    .join(
        association_table, 
        (Course.id == association_table.c.course_id) & (association_table.c.student_id == 2),
    )
)

这将导致 sql 与以下相似：

SELECT  course.id,
        course.name
FROM    course
   JOIN association
     ON course.id = association.course_id
    AND association.student_id = ?

Answer 2

更新：我找到了一个可行的解决方案，实际上很简单。
就像：

session.query(Course).join(association_table).filter_by(student_id=view_kwargs["student_id"])