[Django] #25113: Field lookup for __not_in
Django
----------------------------------------------+--------------------
Reporter: tschuy | Owner: nobody
Type: New feature | Status: new
Component: Database layer (models, ORM) | Version: master
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 1
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25113>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: tschuy | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by tschuy):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> 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.
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/25113#comment:1>
Django
Reporter: tschuy | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Description changed by tschuy:
Old description:
> 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.
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/25113#comment:2>
Django
Reporter: tschuy | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Description changed by tschuy:
Old description:
> 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.
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/25113#comment:3>
Django
Reporter: tschuy | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Comment (by coldmind):
Using `.exclude()` with `in` lookup (.exclude(id__in=[...])) will be
enough instead of creating new API
--
Ticket URL: <https://code.djangoproject.com/ticket/25113#comment:4>
Django
Reporter: tschuy | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Changes (by tschuy):
* status: new => closed
* resolution: => wontfix
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25113#comment:5>
Django
Reporter: Evan Tschuy | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: wontfix
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Comment (by Dmitry Mugtasimov):
It produce SQL like:
{{{
NOT ("jobs_job"."id" IN (SELECT U0."id" FROM "jobs_jobstatus" U0))
}}}
which may not be equivalent to
{{{
"jobs_job"."id" NOT IN (SELECT U0."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('id',
flat=True).all()),
...
)
}}}
instead of
{{{
Job.objects.filter(...).exclude(id__in=JobStatus.objects.values_list('id',
flat=True).all()).delete()
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25113#comment:6>
Django
Reporter: Evan Tschuy | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: wontfix
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Comment (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`.
--
Ticket URL: <https://code.djangoproject.com/ticket/25113#comment:7>
Django
Reporter: Evan Tschuy | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: wontfix
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Comment (by Simon Charette):
From my point of view
[https://code.djangoproject.com/ticket/5763#comment:19 this feature would
have the same flaws] as the ''wontfixed'' `__ne` lookup request (#5763)
and thus should follow the same resolution.
--
Ticket URL: <https://code.djangoproject.com/ticket/25113#comment:8>