26

I have to filter a queryset by a dynamic value (which can be None): may I simply write:

filtered_queryset = queryset.filter(field=value)

or shall I check for None:

if value is None:
    filtered_queryset = queryset.filter(field__isnull=True)
else:
    filtered_queryset = queryset.filter(field=value)

Does the behaviour depend on the particular DBMS?

Don
  • 16,928
  • 12
  • 63
  • 101

2 Answers2

31

The ORM will handle None (cast it to NULL) for you and return a QuerySet object, so unless you need to catch None input the first example is fine.

>>> User.objects.filter(username=None)
[]
>>> type(_)
<class 'django.db.models.query.QuerySet'>
>>> str(User.objects.filter(username=None).query)
SELECT "auth_user"."id", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."password", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."is_superuser", "auth_user"."last_login", "auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."username" IS NULL
Hedde van der Heide
  • 21,841
  • 13
  • 71
  • 100
  • 1
    +1 I was unaware it handled `None` like that, very useful to know. – Ngenator May 03 '13 at 12:48
  • 4
    I'm using Django 1.7 and both cases generate the same query: In [19]: str(User.objects.filter(username__isnull=True).query) == str(User.objects.filter(username=None).query) Out[19]: True So, I suppose you can use either. – Akhorus Feb 05 '15 at 19:06
  • Do note that `None` is ***not*** handled correctly if you use the `__in` lookup. For example, `filter(something__in=[None, 1])` does not work as expected. See e.g. [Django ticket 20024](https://code.djangoproject.com/ticket/20024) and [this SO question](https://stackoverflow.com/q/60392280). – djvg Dec 21 '22 at 16:53
6

I prefer the second solution which is handled better

Update

if value is None:
    filtered_queryset = queryset.filter(field__isnull=True)
    # Do some proessing with  filtered_queryset object with None values
else:
    filtered_queryset = queryset.filter(field=value)
    # Do some proessing with  filtered_queryset object with not NULL values

Query set can handle Null values..Based on this User.objects.filter(username=None) this would fetch only values where username=NULL

Stefan_EOX
  • 1,279
  • 1
  • 16
  • 35
Rajeev
  • 44,985
  • 76
  • 186
  • 285
  • The only reason which suggested your second condition was if u wanted to handle None conditions and do some processing on it then u could have used the second solution.Please see the updated answer.. – Rajeev May 06 '13 at 05:38
  • Thanks. One last question: when you say "this would fetch only values where username=NULL", you mean that Django would translate into "username IS NULL" in case it is required by the DB backend? – Don May 06 '13 at 08:41
  • For example `1)queryset = MyModel.objects.all()` `2)print queryset.query` `3)SELECT "app_mymodel"."id", ... FROM "app_mymodel"` – Rajeev May 06 '13 at 09:44