在 Django QuerySet 中，如何筛选多对一关系中的“不存在”

我有两个这样的模型:

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 = ???

我希望你能帮我个忙？过滤掉没有相关报表的用户。我要怎么做？如果这不可能，因为我已经介绍了它，什么是替代方法？

71327 次浏览
小开

To filter users who do not have reports associated with them try this:

users = User.objects.exclude(id__in=[elem.user.id for elem in Report.objects.all()])

小开
最佳答案

Note: this answer was written in 2013 for Django 1.5. See the other answers for better approaches that work with newer versions of Django

Use isnull.

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

When you use isnull=False, the distinct() is required to prevent duplicate results.

小开

The only way to get native SQL EXISTS/NOT EXISTS without extra queries or JOINs is to add it as raw SQL in the .extra() clause:

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)
])

In fact, it's a pretty obvious and efficient solution and I sometimes wonder why it wasn't built in to Django as a lookup. Also it allows to refine the subquery to find e.g. only users with[out] a report during last week, or with[out] an unanswered/unviewed report.

小开

Alasdair's answer is helpful, but I don't like using distinct(). It can sometimes be useful, but it's usually a code smell telling you that you messed up your joins.

Luckily, Django's queryset lets you filter on subqueries. With Django 3.0, you can also use an exists clause.

Here are a few ways to run the queries from your question:

# 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()

If you put that into a Python file and run it, you should see something like this:

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.

You can see that the final query uses all inner joins.

小开

As of Django 3.0 you can now use expressions directly in a filter(), removing the unnecessary SQL clause:

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%';

Docs:

For Django 1.11+ you can add EXISTS subqueries:

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

This generates SQL something like this:

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);

A NOT EXISTS clause is almost always the most efficient way to do a "not exists" filter.

小开

In addition to @OrangeDog answer. Since Django 3.0 you can use Exists subquery to directly filter a queryset:

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

The best option for finding rows where there is a joining row:

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

This uses an INNER JOIN (and then redundantly checks User.id is not null).

The best option for finding rows where there is not a joining row:

Report.objects.filter(user__isnull=True)

This makes LEFT OUTER JOIN, then checks User.id is not null.

Queries based on joins will be quicker than subqueries, so this is quicker than newly available options such as in Django >= 3, for finding rows without a joining row:

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

This creates a WHERE NOT EXISTS (SELECT .. FROM User..) so involves a potentially large intermediate result set (thanks @Tomasz Gandor).

This for Django <3, where filter() can't be passed subqueries, also uses a subquery so is slower:

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

This can be combined with subqueries. In this example, a Textbook has a number of Versions (ie, version has textbook_id), and a version has a number of Pages (ie, page has version_id). The subquery gets the latest version of each textbook that has pages associated:

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()

To return rows that have a join to one or both of two tables, use Q objects (Page and TextMarkup both have nullable foreign keys joining to File):

from django.db.models import Q


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

提交答案