<p>我认为最简单的方法是定义一个自定义查找,类似于<a href="https://docs.djangoproject.com/en/3.0/howto/custom-lookups/#a-lookup-example" rel="noreferrer">this one</a>或<a href="https://github.com/django/django/blob/master/django/db/models/lookups.py#L346" rel="noreferrer">the in lookup</a></p>
<pre class="lang-py prettyprint-override"><code>from django.db.models.lookups import In as LookupIn
class NotIn(LookupIn):
lookup_name = "notin"
def get_rhs_op(self, connection, rhs):
return "NOT IN %s" % rhs
Field.register_lookup(NotIn)
</code></pre>
<p>或</p>
<pre class="lang-py prettyprint-override"><code>class NotIn(models.Lookup):
lookup_name = "notin"
def as_sql(self, compiler, connection):
lhs, params = self.process_lhs(compiler, connection)
rhs, rhs_params = self.process_rhs(compiler, connection)
params.extend(rhs_params)
return "%s NOT IN %s" % (lhs, rhs), params
</code></pre>
<p>然后在查询中使用它:</p>
<pre class="lang-py prettyprint-override"><code>query = (
JobLog.objects.values(
"username", "job_number", "name", "time",
)
.filter(time__gte=start, time__lte=end, event="delivered")
.filter(
job_number__notin=models.Subquery(
JobLog.objects.values_list("job_number", flat=True).filter(
time__gte=start, time__lte=end, event="finished",
)
)
)
)
</code></pre>
<p>这将生成SQL:</p>
<pre class="lang-sql prettyprint-override"><code>SELECT
"people_joblog"."username",
"people_joblog"."job_number",
"people_joblog"."name",
"people_joblog"."time"
FROM
"people_joblog"
WHERE ("people_joblog"."event" = delivered
AND "people_joblog"."time" >= 2020 - 03 - 13 15:24:34.691222 + 00:00
AND "people_joblog"."time" <= 2020 - 03 - 13 15:24:41.678069 + 00:00
AND "people_joblog"."job_number" NOT IN (
SELECT
U0. "job_number"
FROM
"people_joblog" U0
WHERE (U0. "event" = finished
AND U0. "time" >= 2020 - 03 - 13 15:24:34.691222 + 00:00
AND U0. "time" <= 2020 - 03 - 13 15:24:41.678069 + 00:00)))
</code></pre>