I’ve been racking my brain trying to get the ORM to do an equivalent of a SQL
NOT IN clause. Unfortunately, I haven’t been able to with either
exclude() or a
Q object. These both result in a clause such as:
NOT (id IN ())
id NOT IN ()
There are more details here: https://stackoverflow.com/questions/60671987/django-orm-equivalent-of-sql-not-in-exclude-and-q-objects-do-not-work
But am I missing something obvious? Or should I just drop to raw SQL here? Thanks in advance for having a look if this is in your wheelhouse.
You should be able to make a custom lookup that inherits from
In and replaces it with
Thank you, thank you, THANK YOU.
TIL: Custom Lookups!
Why do you specifically need ‘NOT IN’? The results differ because of
NULLs? Is it something we should consider adding to Django?
I’m working on the P.R. right now, as a matter of fact! The result of
NOT (id IN (a, b, c)) is not always the same as
id NOT IN (a, b, c). That is the disparity that was hitting me. I’m not the first to notice the subtle difference:
The details at the Stack Overflow post show the specific use case - a logging table from Univa Grid Engine (nee Oracle Grid Engine, nee Sun Grid Engine). To find jobs that are currently running, we need to find rows that have a “delivered” event, that do not have a “finished” event. To find jobs that are currently queued, we need to find rows that have a “pending” event, that do not have a “delivered” event. The
NOT IN with a
Subquery is working wonderfully for that now.
Thanks again for your help. I’d love it if you could review the code before I P.R. it!
Could you try to come up with a simplified test case that demonstrates why this is needed beyond possible optimizations? I’m pretty sure @adamchainz’s intuition is right wrt to
NULL handling and if that’s the case you just need to adjust your subquery or outer query lookup to deal which such value to achieve the same results.
To me this is a similar problem to the often proposed
__ne lookup mapping to
!= that we’ve decided not to include in Django core to avoid confusion wrt to
NULL handling. This comment from Anssi clearly explains why this not a good idea; it breaks
exclude complementary when
NULL values are involved.
This is explained in more details in https://stackoverflow.com/questions/129077/null-values-inside-not-in-clause