SqlAlchemy:我想过滤两个外键上的查询

Question

我有两个表（比如Student和College），还有第三个表，它有student_id和college_id外键。你知道吗

我不想硬删除Student和College行，因此在这两个表中都包含了deleted(Boolean)列。你知道吗

我想将一个学生添加到学院（通过将student_id和college_id添加到第三个表）。我首先要检查该学生是否已经存在于学院中，同时还要过滤已删除的学生和学院记录。你知道吗

我写的第一部分是：

db.session.query(StudentCollegeMappingModel).filter(
    StudentCollegeMappingModel.student_id==student_id,
    StudentCollegeMappingModel.college_id==college_id
).first()

但我也想看看

Student.deleted==False and College.deleted==False

我该怎么做？你知道吗

Answer 1

可以使用连接

db.session.query(StudentCollegeMappingModel, Student, College)
    .join(StudentCollegeMappingModel)
    .join(Student)
    .join(College)
    .filter(StudentCollegeMappingModel.student_id == Student.id, StudentCollegeMappingModel.college_id==college_id,Student.deleted==False, College.deleted==False)
    ).all()

Answer 2

您需要将Student和College表连接到查询中，以便对deleted列进行筛选：

query = (
    StudentCollegeMappingModel.query
    .join(Student, Student.id == StudentCollegeMappingModel.student_id)
    .join(College, College.id == StudentCollegeMappingModel.college_id)
    .filter(
        StudentCollegeMappingModel.student_id == student_id,
        StudentCollegeMappingModel.college_id == college_id,
        Student.deleted.is_(False),
        College.deleted.is_(False),
    )
)

我使用了StudentCollegeMappingModel.query而不是db.session.query(StudentCollegeMappingModel)；它们产生相同的初始Query实例，但是SQLAlchemy通过给你.query属性来让你更容易。你知道吗

如果您将student_id和college_id配置为ForeignKey()列，并且表之间没有其他可能混淆SQLAlchemy的关系，那么您可能可以省略这里的Student.id == StudentCollegeMappingModel.student_id和College.id == StudentCollegeMappingModel.college_idON子句：

query = (
    StudentCollegeMappingModel.query.join(Student).join(College)
    .filter(
        StudentCollegeMappingModel.student_id == student_id,
        StudentCollegeMappingModel.college_id == college_id,
        Student.deleted.is_(False),
        College.deleted.is_(False),
    )
)

也可以使用^{} with ^{}：

query = (
    StudentCollegeMappingModel.query.join(Student).join(College)
    .filter(
        StudentCollegeMappingModel.student_id == student_id,
        StudentCollegeMappingModel.college_id == college_id,
        Student == false(),
        College == false(),
    )
)

请注意，在插入新记录时，可以将上述查询用作NOT EXISTS筛选器；这将有助于防止争用条件（多段代码尝试进行相同的更改，或者一些代码在测试后但在插入之前设置deleted列）。你知道吗