CODEWITHSUNDEEP
djangodjango-modelsdjango-rest-framework
John
John

Reputation: 299

Django Q object is adding extra - IS NOT NULL

for the below raw query, I want to build Django ORM query

Select * from employee 
WHERE (address_id is not null OR address_id<>'*')
    AND (xyz_id is null OR xyz_id='*')
    AND (abc_id is null OR abc_id='*')

So I have build ORM query as below

data = Employee.objects.filter(~Q(address_id="23") | Q(address_id__isnull=False),
                            Q(xyz_id__isnull=False) | Q(xyz_id='*'),
                            Q(abc_id__isnull=True) | Q(abc_id=‘*’))

and when i print the query, it is showing as below

SELECT "employee"."id", "employee"."xyz_id", "employee"."abc_id" FROM "employee" WHERE ((NOT ("employee"."address_id" = 23 AND "employee"."address_id" IS NOT NULL) OR "employee"."address_id" IS NOT NULL) AND ("employee"."xyz_id" IS NOT NULL OR "employee"."xyz_id" = *) AND ("employee"."abc_id" IS NULL OR "employee"."abc_id" = *))

my question is why Q object in Django is adding bolded line in above generated query ? which is not solving for above raw sql criteria.

i am using Django 2.2 version

Kindly advise

Upvotes: 3

Views: 419

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477265

If you write Q(foo=bar), then an implicit constraint is that foo is not NULL, if you negate that however, it will write NOT (foo = bar) but if foo is NULL, then NOT (foo = bar) is NOT (NULL = bar) which is NULL, so this will be filtered out of the queryset. which would thus result in FALSE whereas for NULL, one would expect that this is TRUE.

To thus ensure that ~Q(…) is the full opposite of Q(…), it thus should add foo IS NOT NULL to the condition.

In this specific case however the query optimizer could probably indeed figure out that it is not necessary due to the address_id IS NOT NULL later in the query, but here apparently the optimizer does not eliminate that clause.

Upvotes: 1

Related Questions