Opened 7 years ago

Closed 7 years ago

Last modified 2 years ago

#25113 closed New feature (wontfix)

Field lookup for __not_in

Reported by: Evan Tschuy Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Evan Tschuy)

Making a query where I can get all of the objects matching a certain list is quite useful. However, Django currently doesn't allow me to get objects that DON'T match a list:

test = ProductPreparation.objects.filter(product=product, preparation__id__not_in=preparations)

I feel like adding a field lookup for __not_in would be helpful. I've created a pull request on Github:

https://github.com/django/django/pull/4984

I've not added any tests to it, as I couldn't find any tests for __in, and don't quite know how to test an entire field lookup. Apologies; would love pointers.

Change History (8)

comment:1 Changed 7 years ago by Evan Tschuy

Description: modified (diff)

comment:2 Changed 7 years ago by Evan Tschuy

Description: modified (diff)

comment:3 Changed 7 years ago by Evan Tschuy

Description: modified (diff)

comment:4 Changed 7 years ago by Andriy Sokolovskiy

Using .exclude() with in lookup (.exclude(id__in=[...])) will be enough instead of creating new API

Last edited 7 years ago by Andriy Sokolovskiy (previous) (diff)

comment:5 Changed 7 years ago by Evan Tschuy

Resolution: wontfix
Status: newclosed

ah, chaining it like so:

test = ProductPreparation.objects.filter(product=product).exclude(preparation__id__in=preparations)

Of course. Thanks! I'll go ahead and close the ticket.

comment:6 Changed 3 years ago by Dmitry Mugtasimov

It produce SQL like:

NOT ("jobs_job"."id" IN (SELECT U0."job_id" FROM "jobs_jobstatus" U0))

which may not be equivalent to

"jobs_job"."id" NOT IN (SELECT U0."job_id" FROM "jobs_jobstatus" U0)

regarding performance.

Also because of "weird" chained filter() behavior ( https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships ) you will have to do something like

Job.objects.filter(
            ~Q(id__in=JobStatus.objects.values_list('job_id', flat=True).all()),
            ...
        )

instead of

Job.objects.filter(...).exclude(id__in=JobStatus.objects.values_list('job_id', flat=True).all()).delete()
Last edited 3 years ago by Dmitry Mugtasimov (previous) (diff)

comment:7 Changed 2 years ago by Timothy Allen

I ran into this same issue. As the comment above points out, NOT (id IN ()) is not equivalent to id NOT IN (). Details of my issue and attempts are here:

https://stackoverflow.com/questions/60671987/django-orm-equivalent-of-sql-not-in-exclude-and-q-objects-do-not-work

The solution provided on that post, and another provided on Django's forum, seem like a reasonable addition to Django. Lacking support for the equivalent of SQL's id NOT IN (1, 2, 3) is a hole in the ORM feature set, especially now that we have Subquery.

comment:8 Changed 2 years ago by Simon Charette

From my point of view this feature would have the same flaws as the wontfixed __ne lookup request (#5763) and thus should follow the same resolution.

Note: See TracTickets for help on using tickets.
Back to Top