回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p><strong>问题</strong></p>
<p>我试图使用Django ORM来执行SQL <code>NOT IN</code>子句的等效操作,在子选择中提供一个ID列表,以从日志记录表中返回一组记录。我不知道这是否可能</p>
<p><strong>模型</strong></p>
<pre class="lang-py prettyprint-override"><code>class JobLog(models.Model):
job_number = models.BigIntegerField(blank=True, null=True)
name = models.TextField(blank=True, null=True)
username = models.TextField(blank=True, null=True)
event = models.TextField(blank=True, null=True)
time = models.DateTimeField(blank=True, null=True)
</code></pre>
<p><strong>我尝试过的</strong></p>
<p>我的第一次尝试是使用<code>exclude</code>,但这并不<code>NOT</code>否定整个<code>Subquery</code>,而不是所需的<code>NOT IN</code>:</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")
.exclude(
job_number__in=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 "view_job_log"."username", "view_job_log"."group", "view_job_log"."job_number", "view_job_log"."name", "view_job_log"."time"
FROM "view_job_log"
WHERE (
"view_job_log"."event" = 'delivered'
AND "view_job_log"."time" >= '2020-03-12T11:22:28.300590+00:00'::timestamptz
AND "view_job_log"."time" <= '2020-03-13T11:22:28.300600+00:00'::timestamptz
AND NOT (
"view_job_log"."job_number" IN (
SELECT U0."job_number"
FROM "view_job_log" U0
WHERE (
U0."event" = 'finished' AND U0."time" >= '2020-03-12T11:22:28.300590+00:00'::timestamptz
AND U0."time" <= '2020-03-13T11:22:28.300600+00:00'::timestamptz
)
)
AND "view_job_log"."job_number" IS NOT NULL
)
)
</code></pre>
<p>我需要的是第三个<code>AND</code>子句是<code>AND "view_job_log"."job_number" NOT IN</code>,而不是<code>AND NOT (</code></p>
<p>我还尝试先使用<code>exclude</code>作为自己的查询执行子选择,如下所示:</p>
<p><a href="https://stackoverflow.com/questions/9003518/django-equivalent-of-sql-not-in">Django equivalent of SQL not in</a></p>
<p>然而,这会产生同样的问题结果。然后我尝试了一个<code>Q</code>对象,它产生了一个类似的查询:</p>
<pre class="lang-py prettyprint-override"><code>query = (
JobLog.objects.values(
"username", "subscriber_code", "job_number", "name", "time",
)
.filter(
~models.Q(job_number__in=models.Subquery(
JobLog.objects.values_list("job_number", flat=True).filter(
time__gte=start, time__lte=end, event="finished",
)
)),
time__gte=start,
time__lte=end,
event="delivered",
)
)
</code></pre>
<p>使用<code>Q</code>对象的这种尝试同样会产生以下SQL,而不使用<code>NOT IN</code>:</p>
<pre class="lang-sql prettyprint-override"><code>SELECT "view_job_log"."username", "view_job_log"."group", "view_job_log"."job_number", "view_job_log"."name", "view_job_log"."time"
FROM "view_job_log" WHERE (
NOT (
"view_job_log"."job_number" IN (
SELECT U0."job_number"
FROM "view_job_log" U0
WHERE (
U0."event" = 'finished'
AND U0."time" >= '2020-03-12T11:33:28.098653+00:00'::timestamptz
AND U0."time" <= '2020-03-13T11:33:28.098678+00:00'::timestamptz
)
)
AND "view_job_log"."job_number" IS NOT NULL
)
AND "view_job_log"."event" = 'delivered'
AND "view_job_log"."time" >= '2020-03-12T11:33:28.098653+00:00'::timestamptz
AND "view_job_log"."time" <= '2020-03-13T11:33:28.098678+00:00'::timestamptz
)
</code></pre>
<p>有没有办法让Django的ORM做一些与<code>AND job_number NOT IN (12345, 12346, 12347)</code>等价的事情?还是我必须使用原始SQL来完成这项任务</p>
<p>提前感谢您阅读这整道文字墙问题。显式比隐式好。:)</p>