Field lookup for __not_in

[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.

[Andriy Sokolovskiy]

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

[Evan Tschuy]

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.

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

[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.

[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.