3

I have the same problem as OP in this thread: Django - filtering by "certain value or None":

I have a field in an data object that can be either null or set to some integer. I want, given an integer, to filter all the objects with that integer value OR none:

MyElements.objects.all().filter(value__in=[myInt, None]) # Query 1

However, this line does not work for elements with null value. More precisely:

MyElements.objects.all().filter(value__in=[None]) # Query 2

returns nothing. whereas

MyElements.objects.all().filter(value = None) # Query 3

returns the null-valued elements.

How can I rewrite the original query (which involves myInt) correctly?

I am aware that OP has accepted an answer, but I would like to know why Query 2 did not yield any results.

Be Chiller Too
  • 2,502
  • 2
  • 16
  • 42
  • https://stackoverflow.com/questions/9581745/sql-is-null-and-null - after all, there always needs to be a query generated. – mfrackowiak Mar 25 '19 at 14:05

2 Answers2

2

It is based on SQL rules for execution. NULL values are not actual values to be compared. it's an unknow value.

read more about this here: is null vs == null

In Django you can get records with NULL values like that:

MyElements.objects.filter(value__isnull = True)

So you need to first filter None out your list of comparisons and add it to your query by yourself. 

from django.db.models import Q
MyElements.objects.filter(Q(value__in = [list_of_vals]) | Q(value__isnull = True))
Ramy M. Mousa
  • 5,727
  • 3
  • 34
  • 45
1

You should test if a field is null using the isnull filter instead; otherwise in SQL any value compared to a null value would always evaluate as false:

from django.db.models import Q
MyElements.objects.filter(Q(value=myInt) | Q(value__isnull=True))
blhsing
  • 91,368
  • 6
  • 71
  • 106