Django: filtering queryset by 'field__isnull=True' or 'field=None'?
32,843
Solution 1
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
Solution 2
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
Related videos on Youtube
Author by
Don
Curious developer, looking for practical solutions to practical problems...
Updated on November 22, 2021Comments
-
Don over 2 years
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?
-
Ngenator about 11 years+1 I was unaware it handled
None
like that, very useful to know. -
Rajeev about 11 yearsThe 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..
-
Don about 11 yearsThanks. 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?
-
Rajeev about 11 yearsFor example
1)queryset = MyModel.objects.all()
2)print queryset.query
3)SELECT "app_mymodel"."id", ... FROM "app_mymodel"
-
Akhorus over 9 yearsI'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.