tutorial - En un Django QuerySet, cómo filtrar por "no existe" en una relación de muchos a uno

the django project

Question

Tengo dos modelos como este:

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

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

En realidad, cada modelo tiene más campos que no tienen consecuencias para esta pregunta.

Quiero filtrar todos los usuarios que tienen un correo electrónico que comienza con ''a'' y no tiene informes. Habrá más .filter() y .exclude() basados ​​en otros campos.

Quiero abordarlo así:

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

users = users.filter(<other filters>)

users = ???

Me gustaría ??? para filtrar a los usuarios que no tienen informes asociados a ellos. ¿Cómo haría esto? Si esto no es posible, tal como lo presenté, ¿qué es un enfoque alternativo?

Answer 1

La única forma de obtener SQL EXISTS / NOT EXISTS sin consultas adicionales o JOINs es agregarlo como SQL sin procesar en la cláusula .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)
])

De hecho, es una solución bastante obvia y eficiente, y a veces me pregunto por qué no fue incorporada a Django como una búsqueda. También permite refinar la subconsulta para encontrar, por ejemplo, solo usuarios con [un] informe durante la semana pasada , o con [un informe] no contestado o no visto.

Answer 2

Para filtrar los usuarios que no tienen informes asociados, intente esto:

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

Answer 3

Use isnull .

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

Cuando utiliza isnull=False , se requiere distinct() para evitar resultados duplicados.

Answer 4

La respuesta de Alasdair es útil, pero no me gusta usar distinct() . A veces puede ser útil, pero generalmente es un olor a código que te dice que has estropeado tus uniones.

Afortunadamente, el queryset de queryset de Django queryset permite filtrar subconsultas.

Aquí hay algunas maneras de ejecutar las consultas de su pregunta:

# Tested with Django 1.9.2
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.base import ModelBase

NAME = ''udjango''


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)

    syncdb(User)
    syncdb(Report)

    anne = User.objects.create(email=''[email protected]'')
    User.objects.create(email=''[email protected]'')
    alice = User.objects.create(email=''[email protected]'')
    User.objects.create(email=''[email protected]'')

    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(''Done.'')


def setup():
    db_file = NAME + ''.db''
    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__

    # noinspection PyDecorator
    @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()

Si pones eso en un archivo Python y lo ejecutas, deberías ver algo como esto:

2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) PRAGMA foreign_keys; args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) PRAGMA foreign_keys = 0; args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) BEGIN; args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.schema.execute(): CREATE TABLE "udjango_user" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "email" varchar(254) NOT NULL); (params None)
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) CREATE TABLE "udjango_user" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "email" varchar(254) NOT NULL); args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) PRAGMA foreign_keys = 0; args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) PRAGMA foreign_keys; args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) PRAGMA foreign_keys = 0; args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) BEGIN; args=None
2017-10-06 09:56:22[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")); (params None)
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) CREATE TABLE "udjango_report" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "user_id" integer NOT NULL REFERENCES "udjango_user" ("id")); args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.schema.execute(): CREATE INDEX "udjango_report_e8701ad4" ON "udjango_report" ("user_id"); (params [])
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) CREATE INDEX "udjango_report_e8701ad4" ON "udjango_report" ("user_id"); args=[]
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) PRAGMA foreign_keys = 0; args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) BEGIN; args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) INSERT INTO "udjango_user" ("email") VALUES (''[email protected]''); args=[''[email protected]'']
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) BEGIN; args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) INSERT INTO "udjango_user" ("email") VALUES (''[email protected]''); args=[''[email protected]'']
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) BEGIN; args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) INSERT INTO "udjango_user" ("email") VALUES (''[email protected]''); args=[''[email protected]'']
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) BEGIN; args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) INSERT INTO "udjango_user" ("email") VALUES (''[email protected]''); args=[''[email protected]'']
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) BEGIN; args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) INSERT INTO "udjango_report" ("user_id") VALUES (1); args=[1]
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) BEGIN; args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) INSERT INTO "udjango_report" ("user_id") VALUES (3); args=[3]
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) BEGIN; args=None
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) INSERT INTO "udjango_report" ("user_id") VALUES (3); args=[3]
2017-10-06 09:56:22[INFO]root.main(): users without reports
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (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_report"."id" IS NULL AND "udjango_user"."email" LIKE ''a%'' ESCAPE ''/') LIMIT 21; args=(u''a%'',)
2017-10-06 09:56:22[INFO]root.main(): [User(u''[email protected]'')]
2017-10-06 09:56:22[INFO]root.main(): users with reports (allows duplicates)
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (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_report"."id" IS NOT NULL AND "udjango_user"."email" LIKE ''a%'' ESCAPE ''/') LIMIT 21; args=(u''a%'',)
2017-10-06 09:56:22[INFO]root.main(): [User(u''[email protected]''), User(u''[email protected]''), User(u''[email protected]'')]
2017-10-06 09:56:22[INFO]root.main(): users with reports (no duplicates)
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (0.000) SELECT "udjango_user"."id", "udjango_user"."email" FROM "udjango_user" WHERE (NOT ("udjango_user"."id" IN (SELECT U0."id" AS Col1 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=(u''a%'',)
2017-10-06 09:56:22[INFO]root.main(): [User(u''[email protected]''), User(u''[email protected]'')]
2017-10-06 09:56:22[INFO]root.main(): users with reports (no duplicates, simpler SQL)
2017-10-06 09:56:22[DEBUG]django.db.backends.execute(): (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=(u''a%'',)
2017-10-06 09:56:22[INFO]root.main(): [User(u''[email protected]''), User(u''[email protected]'')]
2017-10-06 09:56:22[INFO]root.main(): Done.

Puede ver que la consulta final usa todas las combinaciones internas.