<p>您需要将<code>Student</code>和<code>College</code>表连接到查询中,以便对<code>deleted</code>列进行筛选:</p>
<pre><code>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),
)
)
</code></pre>
<p>我使用了<code>StudentCollegeMappingModel.query</code>而不是<code>db.session.query(StudentCollegeMappingModel)</code>;它们产生相同的初始<code>Query</code>实例,但是SQLAlchemy通过给你<code>.query</code>属性来让你更容易。你知道吗</p>
<p>如果您将<code>student_id</code>和<code>college_id</code>配置为<code>ForeignKey()</code>列,并且表之间没有其他可能混淆SQLAlchemy的关系,那么您可能可以省略这里的<code>Student.id == StudentCollegeMappingModel.student_id</code>和<code>College.id == StudentCollegeMappingModel.college_id</code><code>ON</code>子句:</p>
<pre><code>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),
)
)
</code></pre>
<p>也可以使用<a href="https://stackoverflow.com/questions/18998010/flake8-complains-on-boolean-comparison-in-filter-clause">^{<cd14>} with ^{<cd15>}</a>:</p>
<pre><code>query = (
StudentCollegeMappingModel.query.join(Student).join(College)
.filter(
StudentCollegeMappingModel.student_id == student_id,
StudentCollegeMappingModel.college_id == college_id,
Student == false(),
College == false(),
)
)
</code></pre>
<p>请注意,在插入新记录时,可以将上述查询用作<code>NOT EXISTS</code>筛选器;这将有助于防止争用条件(多段代码尝试进行相同的更改,或者一些代码在测试后但在插入之前设置<code>deleted</code>列)。你知道吗</p>