问题
我试图使用Django ORM来执行SQL NOT IN
子句的等效操作,在子选择中提供一个ID列表,以从日志记录表中返回一组记录。我不知道这是否可能
模型
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)
我尝试过的
我的第一次尝试是使用exclude
,但这并不NOT
否定整个Subquery
,而不是所需的NOT IN
:
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",
)
)
)
)
不幸的是,这会产生以下SQL:
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
)
)
我需要的是第三个AND
子句是AND "view_job_log"."job_number" NOT IN
,而不是AND NOT (
我还尝试先使用exclude
作为自己的查询执行子选择,如下所示:
Django equivalent of SQL not in
然而,这会产生同样的问题结果。然后我尝试了一个Q
对象,它产生了一个类似的查询:
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",
)
)
使用Q
对象的这种尝试同样会产生以下SQL,而不使用NOT IN
:
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
)
有没有办法让Django的ORM做一些与AND job_number NOT IN (12345, 12346, 12347)
等价的事情?还是我必须使用原始SQL来完成这项任务
提前感谢您阅读这整道文字墙问题。显式比隐式好。:)
我认为最简单的方法是定义一个自定义查找,类似于this one或the in lookup
或
然后在查询中使用它:
这将生成SQL:
通过使用
Exists
和特殊的大小写NULL
可以获得相同的结果你能试试这个吗
相关问题 更多 >
编程相关推荐