ORM | not_in equivalent

Using Django
1

Hi,

I need to achieve an not_in beaviour, but dont know how as ‘exclude’ and ‘~Q(..)’, doesnt provide it.

Lets Assume:
c=Customer.objects.filter(id__in=list_of_customers).exclude(id__in=list_of_customers_2)
The SQL statement is:

SELECT "customer"."id", "customer"."fname", "customer"."lname" 
FROM "customer" 
WHERE ("customer"."id" IN (101, 102) 
    AND NOT ("customer"."id" IN (104, 112)))

Which doesn’t give the same results like:

SELECT "customer"."id", "customer"."fname", "customer"."lname" 
FROM "customer" 
WHERE ("customer"."id" IN (101, 102) 
    AND ("customer"."id" NOT IN (104, 112)))
2

How are they different? Under what condition is an item returned (or not returned) in the first query, but returned in the second?

3

TLDR:
I’m pretty new to django (a few months).
Because I’m new, I prefer an inside solution…
If I have to guess: I will say that the SQL consider the outside ‘NOT’ as a logical statement in other perspective.

Background:
I trying to learn about the ORM, so I taken some old SQL assignment that I have, and translate it to ORM.

Additional:
from a quick search I did find:
https://code.djangoproject.com/ticket/25113#comment:7

(They related)

4

The referenced link is related in that it’s talking about NOT IN, but the difference there - and why it makes a difference - is that they’re talking about it in the context of a subquery.

In your specific case, for the query you’ve provided, there is no difference. The two queries will return the same result.
While you might prefer that the query be written differently, keep in mind that the purpose of you using the ORM is that you don’t need to concern yourself with the query being generated. If it produces the correct result set, then it’s ok.

1 Like