Django ORM:相当于SQL `NOT IN` `？‘`exclude`’和`Q‘对象不起作用
Django ORM:相当于SQL `NOT IN` `？‘`exclude`’和`Q‘对象不起作用
提问于 2020-03-13 14:10:10
问题
我试图使用Django ORM来执行与SQL NOT IN子句等价的操作，在子subselect中提供I列表，以便从日志记录表中带回一组记录。我不知道这是否可能。
模型
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作为自己的查询首先执行子选择，如下所示：
然而，这会产生同样的问题结果。然后我尝试了一个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吗？
提前感谢你阅读了这整篇课文问题。外显好于内隐。:)
回答 3
Stack Overflow用户
回答已采纳
发布于 2020-03-13 15:14:44
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)或
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然后在查询中使用它：
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",
)
)
)
)这将生成SQL：
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)))Stack Overflow用户
发布于 2020-03-14 22:25:07
通过使用Exists和特殊的大小写NULL，您可能会获得相同的结果。
.filter(
~Exists(
JobLog.objects.filter(
Q(jobnumber=None) | Q(jobnumber=OuterRef('jobnumber')),
time__gte=start,
time__lte=end,
event='finished',
)
)
)Stack Overflow用户
发布于 2020-03-13 15:09:16
你能试试这个吗？
JobLog.objects.filter(time__gte=start, time__lte=end, event="delivered").exclude(time__gte=start, event='finished').exclude(time__lte=end, event='finished')页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/60671987
复制相关文章
相似问题
腾讯云开发者
