How do you filter empty or null names in a QuerySet

Gareth D.

The Problem

How can you retrieve models in Django, filtering out those where a specific attribute is null or empty?

The Solution

If you’re using plain Python, you have access to operators such as != to negate an equality clause. But if you’re writing filter() queries in Django, you can only use =. To do more advanced queries, including except queries, you have a few options, including:

• Using exclude
• Using the __isnull field lookup
• Using query objects

Assume you have a User object with an optional bio field that is null by default and set to "" by some users.

class User(models.Model):
    name = models.CharField(max_length=100)
    bio = models.CharField(max_length=1000, blank=True, null=True, default=None)

The following examples show how to select the users with completed bio fields.

Using exclude

The simplest way to exclude all objects where a specific field is null (None) or empty (''), is to chain exclude filters.

To get all the users that have filled out their bio, you could use:

users = User.objects.exclude(bio='').exclude(bio=None)

Using the __isnull field lookup

If you want to filter out only those that are set to NULL (equivalent to SQL IS NOT NULL), use the following. Note that in this case, you’ll still get users with a blank ('') bio field.

users = User.objects.filter(bio__isnull=False)

Using a Q Query

For a simple case like this, you should avoid Django’s advanced Q objects, but they can be useful in more complex cases, and they are easy to negate using ~.

To use Q, you first need to import it:

from django.db.models import Q

Now you can make two queries, one to look for bio=None and one to look for bio=''. You use the or operator (|) to chain them together, and the not operator (~) to invert the query. The following asks for the opposite of all users who have a null or blank bio.

users = User.objects.filter(~(Q(bio=None) | Q(bio='')))