在Django的QuerySet中，如何过滤一个多对一关系中的“不存在”数据？

Question

在Django的QuerySet中，如何过滤一个多对一关系中的“不存在”数据？

101

我有两个这样的模型：

class User(models.Model):
    email = models.EmailField()

class Report(models.Model):
    user = models.ForeignKey(User)

实际上，每个模型都有更多与该问题无关的字段。

我想过滤所有电子邮件以字母'a'开头且没有报告的用户。将会根据其他字段基于更多的.filter()和.exclude()准则。

我想这样做：

users = User.objects.filter(email__like = 'a%')

users = users.filter(<other filters>)

users = ???

我希望能够过滤掉没有关联报告的用户，应该如何实现？如果按照我提出的方式不可能，是否有其他方法可以达到目的？

- Krystian Cybulski

1

[Django 1.11+]的通用解决方案 - OrangeDog

7个回答

100

从Django 3.0开始，您现在可以直接在filter()中使用表达式，消除了不必要的SQL子句：

User.objects.filter(
    ~Exists(Reports.objects.filter(user=OuterRef('pk'))),
    email__startswith='a'
)

SELECT user.pk, user.email
FROM user
WHERE NOT EXISTS (SELECT U0.pk FROM reports U0 WHERE U0.user = user.pk) AND email LIKE 'a%';

文档：

Exists（存在）
OuterRef（外部引用）
Filtering on a Subquery() or Exists() expressions（在Subquery()或Exists()表达式上进行过滤）

对于Django 1.11+，您可以添加EXISTS子查询：

User.objects.annotate(
    no_reports=~Exists(Reports.objects.filter(user__eq=OuterRef('pk')))
).filter(
    email__startswith='a',
    no_reports=True
)

这将生成类似于以下的SQL语句：

SELECT
    user.pk,
    user.email,
    NOT EXISTS (SELECT U0.pk FROM reports U0 WHERE U0.user = user.pk) AS no_reports
FROM user
WHERE email LIKE 'a%' AND NOT EXISTS (SELECT U0.pk FROM reports U0 WHERE U0.user = user.pk);

NOT EXISTS子句几乎总是执行“不存在”过滤的最有效方式。

- OrangeDog

5

这应该是最佳答案。谢谢@OrangeDog。 - pupeno

1

遇到了这个错误：django.core.exceptions.FieldError: Related Field got invalid lookup: eq，使用的是 Django==3.2.7。不得不将 user__eq=OuterRef('pk') 更改为 **user=OuterRef('pk')**，这样就解决了错误。我猜最新版本的 Django 稍有变化。 - Saurav Kumar

12

在没有额外的查询或联表的情况下，获取本地SQL EXISTS/NOT EXISTS的唯一方法是将其作为原始SQL添加到.extra()子句中：

users = users.extra(where=[
    """NOT EXISTS(SELECT 1 FROM {reports} 
                  WHERE user_id={users}.id)
    """.format(reports=Report._meta.db_table, users=User._meta.db_table)
])

实际上，这是一个相当明显和高效的解决方案，我有时会想为什么它没有被内置到Django中作为查找。此外，它允许细化子查询，以便找到例如只在上周有或没有报告的用户，或者有或没有未回答/未查看的报告。

- Yuri Shatrov

2

@OrangeDog params不能用于传递表名。它们将由数据库引擎引用。另外，对表名进行额外的安全处理是没有意义的，因为它们来自代码而不是用户输入。 - Yuri Shatrov

引用表名有什么问题吗？根据它们的不同，可能需要进行引用。这就是你应该使用参数的原因之一。 - OrangeDog

2

@OrangeDog - 因为大多数数据库引擎将单引号解释为字符串字面量 - 表需要用双引号/尖括号引用，或者根本不引用。 - Trent

6

除了 @OrangeDog 的回答外，自 Django 3.0 版本起，你可以使用 Exists 子查询来直接过滤一个查询集：

User.objects.filter(
    ~Exists(Reports.objects.filter(user__eq=OuterRef('pk'))
)

- neverwalkaloner

4

Alasdair的回答很有帮助，但我不喜欢使用distinct()。它有时可能很有用，但通常会让你觉得你的连接出了问题。

幸运的是，Django的查询集允许您在子查询上进行过滤。在Django 3.0中，您还可以使用exists语句。

以下是从您的问题中运行查询的几种方法：

# Tested with Django 3.0 and Python 3.6
import logging
import sys

import django
from django.apps import apps
from django.apps.config import AppConfig
from django.conf import settings
from django.db import connections, models, DEFAULT_DB_ALIAS
from django.db.models import Exists, OuterRef
from django.db.models.base import ModelBase

NAME = 'udjango'
DB_FILE = NAME + '.db'


def main():
    setup()

    class User(models.Model):
        email = models.EmailField()

        def __repr__(self):
            return 'User({!r})'.format(self.email)

    class Report(models.Model):
        user = models.ForeignKey(User, on_delete=models.CASCADE)

    syncdb(User)
    syncdb(Report)

    anne = User.objects.create(email='anne@example.com')
    User.objects.create(email='adam@example.com')
    alice = User.objects.create(email='alice@example.com')
    User.objects.create(email='bob@example.com')

    Report.objects.create(user=anne)
    Report.objects.create(user=alice)
    Report.objects.create(user=alice)

    logging.info('users without reports')
    logging.info(User.objects.filter(report__isnull=True, email__startswith='a'))

    logging.info('users with reports (allows duplicates)')
    logging.info(User.objects.filter(report__isnull=False, email__startswith='a'))

    logging.info('users with reports (no duplicates)')
    logging.info(User.objects.exclude(report__isnull=True).filter(email__startswith='a'))

    logging.info('users with reports (no duplicates, simpler SQL)')
    report_user_ids = Report.objects.values('user_id')
    logging.info(User.objects.filter(id__in=report_user_ids, email__startswith='a'))

    logging.info('users with reports (EXISTS clause, Django 3.0)')
    logging.info(User.objects.filter(
        Exists(Report.objects.filter(user_id=OuterRef('id'))),
        email__startswith='a'))

    logging.info('Done.')


def setup():
    with open(DB_FILE, 'w'):
        pass  # wipe the database
    settings.configure(
        DEBUG=True,
        DATABASES={
            DEFAULT_DB_ALIAS: {
                'ENGINE': 'django.db.backends.sqlite3',
                'NAME': DB_FILE}},
        LOGGING={'version': 1,
                 'disable_existing_loggers': False,
                 'formatters': {
                    'debug': {
                        'format': '%(asctime)s[%(levelname)s]'
                                  '%(name)s.%(funcName)s(): %(message)s',
                        'datefmt': '%Y-%m-%d %H:%M:%S'}},
                 'handlers': {
                    'console': {
                        'level': 'DEBUG',
                        'class': 'logging.StreamHandler',
                        'formatter': 'debug'}},
                 'root': {
                    'handlers': ['console'],
                    'level': 'INFO'},
                 'loggers': {
                    "django.db": {"level": "DEBUG"}}})
    app_config = AppConfig(NAME, sys.modules['__main__'])
    apps.populate([app_config])
    django.setup()
    original_new_func = ModelBase.__new__

    @staticmethod
    def patched_new(cls, name, bases, attrs):
        if 'Meta' not in attrs:
            class Meta:
                app_label = NAME
            attrs['Meta'] = Meta
        return original_new_func(cls, name, bases, attrs)
    ModelBase.__new__ = patched_new


def syncdb(model):
    """ Standard syncdb expects models to be in reliable locations.

    Based on https://github.com/django/django/blob/1.9.3
    /django/core/management/commands/migrate.py#L285
    """
    connection = connections[DEFAULT_DB_ALIAS]
    with connection.schema_editor() as editor:
        editor.create_model(model)


main()

如果你把这段代码放到一个Python文件中并运行它，你应该会看到类似于这样的东西：

2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) PRAGMA foreign_keys = OFF; args=None
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) PRAGMA foreign_keys; args=None
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) BEGIN; args=None
2019-12-06 11:45:17[DEBUG]django.db.backends.schema.execute(): CREATE TABLE "udjango_user" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "email" varchar(254) NOT NULL); (params None)
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) CREATE TABLE "udjango_user" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "email" varchar(254) NOT NULL); args=None
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) PRAGMA foreign_key_check; args=None
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) PRAGMA foreign_keys = ON; args=None
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) PRAGMA foreign_keys = OFF; args=None
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) PRAGMA foreign_keys; args=None
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) BEGIN; args=None
2019-12-06 11:45:17[DEBUG]django.db.backends.schema.execute(): CREATE TABLE "udjango_report" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "user_id" integer NOT NULL REFERENCES "udjango_user" ("id") DEFERRABLE INITIALLY DEFERRED); (params None)
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) CREATE TABLE "udjango_report" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "user_id" integer NOT NULL REFERENCES "udjango_user" ("id") DEFERRABLE INITIALLY DEFERRED); args=None
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) PRAGMA foreign_key_check; args=None
2019-12-06 11:45:17[DEBUG]django.db.backends.schema.execute(): CREATE INDEX "udjango_report_user_id_60bc619c" ON "udjango_report" ("user_id"); (params ())
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) CREATE INDEX "udjango_report_user_id_60bc619c" ON "udjango_report" ("user_id"); args=()
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) PRAGMA foreign_keys = ON; args=None
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.017) INSERT INTO "udjango_user" ("email") VALUES ('anne@example.com'); args=['anne@example.com']
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.023) INSERT INTO "udjango_user" ("email") VALUES ('adam@example.com'); args=['adam@example.com']
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.022) INSERT INTO "udjango_user" ("email") VALUES ('alice@example.com'); args=['alice@example.com']
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.022) INSERT INTO "udjango_user" ("email") VALUES ('bob@example.com'); args=['bob@example.com']
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.029) INSERT INTO "udjango_report" ("user_id") VALUES (1); args=[1]
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.033) INSERT INTO "udjango_report" ("user_id") VALUES (3); args=[3]
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.033) INSERT INTO "udjango_report" ("user_id") VALUES (3); args=[3]
2019-12-06 11:45:17[INFO]root.main(): users without reports
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) SELECT "udjango_user"."id", "udjango_user"."email" FROM "udjango_user" LEFT OUTER JOIN "udjango_report" ON ("udjango_user"."id" = "udjango_report"."user_id") WHERE ("udjango_user"."email" LIKE 'a%' ESCAPE '\' AND "udjango_report"."id" IS NULL) LIMIT 21; args=('a%',)
2019-12-06 11:45:17[INFO]root.main(): <QuerySet [User('adam@example.com')]>
2019-12-06 11:45:17[INFO]root.main(): users with reports (allows duplicates)
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) SELECT "udjango_user"."id", "udjango_user"."email" FROM "udjango_user" INNER JOIN "udjango_report" ON ("udjango_user"."id" = "udjango_report"."user_id") WHERE ("udjango_user"."email" LIKE 'a%' ESCAPE '\' AND "udjango_report"."id" IS NOT NULL) LIMIT 21; args=('a%',)
2019-12-06 11:45:17[INFO]root.main(): <QuerySet [User('anne@example.com'), User('alice@example.com'), User('alice@example.com')]>
2019-12-06 11:45:17[INFO]root.main(): users with reports (no duplicates)
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) SELECT "udjango_user"."id", "udjango_user"."email" FROM "udjango_user" WHERE (NOT ("udjango_user"."id" IN (SELECT U0."id" FROM "udjango_user" U0 LEFT OUTER JOIN "udjango_report" U1 ON (U0."id" = U1."user_id") WHERE U1."id" IS NULL)) AND "udjango_user"."email" LIKE 'a%' ESCAPE '\') LIMIT 21; args=('a%',)
2019-12-06 11:45:17[INFO]root.main(): <QuerySet [User('anne@example.com'), User('alice@example.com')]>
2019-12-06 11:45:17[INFO]root.main(): users with reports (no duplicates, simpler SQL)
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) SELECT "udjango_user"."id", "udjango_user"."email" FROM "udjango_user" WHERE ("udjango_user"."email" LIKE 'a%' ESCAPE '\' AND "udjango_user"."id" IN (SELECT U0."user_id" FROM "udjango_report" U0)) LIMIT 21; args=('a%',)
2019-12-06 11:45:17[INFO]root.main(): <QuerySet [User('anne@example.com'), User('alice@example.com')]>
2019-12-06 11:45:17[INFO]root.main(): users with reports (EXISTS clause, Django 3.0)
2019-12-06 11:45:17[DEBUG]django.db.backends.debug_sql(): (0.000) SELECT "udjango_user"."id", "udjango_user"."email" FROM "udjango_user" WHERE (EXISTS(SELECT U0."id", U0."user_id" FROM "udjango_report" U0 WHERE U0."user_id" = "udjango_user"."id") AND "udjango_user"."email" LIKE 'a%' ESCAPE '\') LIMIT 21; args=('a%',)
2019-12-06 11:45:17[INFO]root.main(): <QuerySet [User('anne@example.com'), User('alice@example.com')]>
2019-12-06 11:45:17[INFO]root.main(): Done.

您可以看到最终查询使用了多个内连接。

- Don Kirkby

看起来你的代码片段可能不完整。最后一条语句是 Report.objects.create(user=anne)，而你的输出和想法表明你打算展示更多的代码。我迫不及待地想看到它！ - Krystian Cybulski

不确定你在说什么，@KrystianCybulski。我的浏览器显示了大约那么多的代码，但是还有一个滚动条可以查看其余部分。如果你的浏览器出现问题，请尝试编辑答案以查看Markdown源代码。 - Don Kirkby

你是正确的。我道歉。Mac上的Chrome很聪明，会隐藏嵌入式代码框中的滚动条。我没有意识到它是可滚动的。 - Krystian Cybulski

1

这，朋友们，才是真正的艺术——不是现代那种“我懂你不懂”的艺术，而是原始的、巧妙地展示你工艺技能掌握的艺术。这不是一招鲇鱼，而是一个带有自包含测试池的钓竿，任何人都可以轻松演示如何投线，或尝试几个快速捕虫的诱饵。干得好，唐。 - Chema

1

要筛选没有关联报告的用户，请尝试以下方法： users = User.objects.exclude(id__in=[elem.user.id for elem in Report.objects.all()])

- Lukasz Koziara

这很有前途。我需要看看会生成什么样的SQL。我认为id__in = Report.objects.all()也足以满足.exclude()的调用。 - Krystian Cybulski

2

如果你只使用users=User.objects.exclude(id__in=Report.objects.all())，你会得到所有id与任何报告id相同的用户。 - Lukasz Koziara

4

除非你只是自己使用网站或与亲密的朋友分享，否则这是一个糟糕的答案；如果你有10亿个报告，这可能会导致数据库崩溃。原则上，永远不要在查询中使用__in=！另外，你甚至没有Report.objects.values('user_id').distinct()，这将对有很多报告但很少用户的情况有所帮助。 - Tomasz Gandor

3

我最好改进一下我的“经验法则”- 只有在可能性数量是恒定的情况下才使用 something__in=[my_value1, my_value2, ...]。这实际上意味着你以某种方式手动指定了它。如果你传递一个 queryset 对象，没关系 - 这实际上会变成一个 SQL 子查询，例如：WHERE "auth_user"."id" in (SELECT U0."id" FROM "report" U0)。因此，你仍然可以希望数据库对其进行理解。 - Tomasz Gandor

2

对于阅读此答案的人来说：[elem.user.id for elem in Report.objects.all()] 是非常低效的 - 它从报告中获取了所有字段（当只需要一个字段时），无缘无故地构建了所有 Report 实例，最后为每个报告执行了额外的 SQL 查询以检索用户... list(Report.objects.values_list('user_id', flat=True)) 可以解决所有这些问题（仍然不是最好的解决方案，但...）。 - bruno desthuilliers

1

查找存在连接行的行的最佳选项：

Report.objects.filter(user__isnull=False).distinct()

这里使用了一个INNER JOIN（然后冗余地检查User.id不为空）。

寻找没有连接行的最佳选项是：

Report.objects.filter(user__isnull=True)

这会执行左外连接，然后检查User.id不为空。基于连接的查询比子查询更快，因此对于查找没有匹配行的行，这比Django >= 3中新可用的选项更快。

Report.objects.filter(~Exists(User.objects.filter(report=OuterRef('pk'))))

这将创建一个 WHERE NOT EXISTS (SELECT .. FROM User..)，因此涉及潜在的大型中间结果集（感谢@Tomasz Gandor）。

对于 Django <3，由于filter()不能传递子查询，因此需要使用一个子查询，因此速度较慢。

Report.objects.annotate(
    no_users=~Exists(User.objects.filter(report=OuterRef('pk')))
).filter(no_users=True)

这可以与子查询结合使用。在此示例中，Textbook（即version具有textbook_id）具有多个Versions，而version具有多个Pages（即page具有version_id）。子查询获取每个关联页面的教材的最新版本：

subquery = (
    Version.objects
        .filter(
            # OuterRef joins to Version.textbook in outer query below
            textbook=OuterRef('textbook'), 
            # excludes rows with no joined Page records
            page__isnull=False)
        # ordered so [:1] below gets highest (ie, latest) version number
        .order_by('-number').distinct()
)
# Only the Version.ids of the latest versions that have pages returned by the subquery
books = Version.objects.filter(pk=Subquery(subquery.values('pk')[:1])).distinct()

为了返回与两个表之一或两者都有连接的行，请使用Q对象（Page和TextMarkup都具有可空外键，连接到File）：

from django.db.models import Q

File.objects.filter(Q(page__isnull=False) | Q(textmarkup__isnull=False).distinct()

- Chris

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Alasdair · Accepted Answer

125

注意：本回答是2013年为Django 1.5编写的。请查看其他回答，以了解适用于新版本Django的更好方法。

使用isnull。

users_without_reports = User.objects.filter(report__isnull=True)
users_with_reports = User.objects.filter(report__isnull=False).distinct()

当您使用isnull=False时，需要使用distinct()来防止重复结果。

- Alasdair

11

这个可以，但当__isnull=True和__isnull=False的情况下将会生成与report表的OUTER JOIN。对于关于拥有报告的用户的问题，它可能比INNER JOIN效率低。我发现了一个丑陋的技巧来解决这个问题：User.objects.filter(report__id__gt=0).distinct()。这假设ID大于0，但这并不一定是正确的。有没有更好的方法来强制使用内部联接？ - Tomasz Gandor

@OrangeDog 如果能解释一下为什么更好，那会很有用，即使只是简单地说“使用NOT EXISTS比连接（通常？）更有效率”。 - Alasdair

@OrangeDog 说得好，但我希望你能确认它是否提供更好的性能。鉴于问题中没有包含任何SQL，我不确定OP是否特别想要使用“NOT EXISTS”的查询。 - Alasdair

2

@Alasdair 是的，使用WHERE NOT EXISTS通常比使用DISTINCT ... OUTER JOIN更快，因为它避免了可能巨大的中间结果集。 - OrangeDog