How to filter empty or NULL names in a QuerySet?

Question

I have first_name, last_name & alias (optional) which I need to search for. So, I need a query to give me all the names that have an alias set.

Only if I could do:

Name.objects.filter(alias!="")

So, what is the equivalent to the above?

Answer 1

You could do this:

Name.objects.exclude(alias__isnull=True)

If you need to exclude null values and empty strings, the preferred way to do so is to chain together the conditions like so:

Name.objects.exclude(alias__isnull=True).exclude(alias__exact='')

Chaining these methods together basically checks each condition independently: in the above example, we exclude rows where alias is either null or an empty string, so you get all Name objects that have a not-null, not-empty alias field. The generated SQL would look something like:

SELECT * FROM Name WHERE alias IS NOT NULL AND alias != ""

You can also pass multiple arguments to a single call to exclude, which would ensure that only objects that meet every condition get excluded:

Name.objects.exclude(some_field=True, other_field=True)

Here, rows in which some_field and other_field are true get excluded, so we get all rows where both fields are not true. The generated SQL code would look a little like this:

SELECT * FROM Name WHERE NOT (some_field = TRUE AND other_field = TRUE)

Alternatively, if your logic is more complex than that, you could use Django's Q objects:

from django.db.models import Q
Name.objects.exclude(Q(alias__isnull=True) | Q(alias__exact=''))

For more info see this page and this page in the Django docs.

_{As an aside: My SQL examples are just an analogy--the actual generated SQL code will probably look different. You'll get a deeper understanding of how Django queries work by actually looking at the SQL they generate.}

Answer 2

Name.objects.filter(alias__gt='',alias__isnull=False)

Answer 3

Firstly, the Django docs strongly recommend not using NULL values for string-based fields such as CharField or TextField. Read the documentation for the explanation:

https://docs.djangoproject.com/en/dev/ref/models/fields/#null

Solution: You can also chain together methods on QuerySets, I think. Try this:

Name.objects.exclude(alias__isnull=True).exclude(alias="")

That should give you the set you're looking for.

Answer 4

1. When using exclude, keep the following in mind to avoid common mistakes:

Should not add multiple conditions into an exclude() block like filter(). To exclude multiple conditions, you should use multiple exclude().

Example: (NOT a AND NOT b)

Entry.objects.exclude(title='').exclude(headline='')

equal to

SELECT... WHERE NOT title = '' AND NOT headline = ''

======================================================

2. Only use multiple when you really know about it:

Example: NOT (a AND b)

Entry.objects.exclude(title='', headline='')

equal to

SELECT.. WHERE NOT (title = '' AND headline = '')

Answer 5

If you want to exclude null (None), empty string (""), as well as a string containing white spaces (" "), you can use the __regex along with __isnull filter option

Name.objects.filter(
    alias__isnull = False, 
    alias__regex = r"\S+"
)

alias__isnull=False excludes all the columns null columns

aliax__regex = r"\S+" makes sure that the column value contains at least one or more non whitespace characters.

Answer 6

From Django 1.8,

from django.db.models.functions import Length

Name.objects.annotate(alias_length=Length('alias')).filter(alias_length__gt=0)

Answer 7

You can simply do this:

Name.objects.exclude(alias="").exclude(alias=None)

It's really just that simple. filter is used to match and exclude is to match everything but what it specifies. This would evaluate into SQL as NOT alias='' AND alias IS NOT NULL.

Answer 8

Another approach using a generic isempty lookup, that can be used with any field.

It can also be used by django rest_framework or other apps that use django lookups:

from distutils.util import strtobool
from django.db.models import Field
from django.db.models.lookups import BuiltinLookup

@Field.register_lookup
class IsEmpty(BuiltinLookup):
    lookup_name = 'isempty'
    prepare_rhs = False

    def as_sql(self, compiler, connection):
        sql, params = compiler.compile(self.lhs)
        condition = self.rhs if isinstance(self.rhs, bool) else bool(strtobool(self.rhs))
        if condition:
            return "%s IS NULL or %s = ''" % (sql, sql), params
        else:
            return "%s <> ''" % sql, params

You can then use it like this:

Name.objects.filter(alias__isempty=False)

Answer 9

this is another simple way to do it .

Name.objects.exclude(alias=None)