Django empty char ('') to null (NULL) inside a Coalesce statement

Question

I have the following query I would like to execute:

MyModel.objects.annotate(current_name=Coalesce('nickname', 'name')).order_by('current_name') 

which fails miserable, because nickname is not NULL when empty, but it is an empty char (as is the convention in the Django community).

Therefore I would like to do something like:

MyModel.objects.annotate(if empty char: make null, then do coalesce like above). Is this possible?

Answer 1

Use Conditional expressions, which are new in Django 1.8 .

from django.db.models import CharField, Case, When
from django.db.models.functions import Coalesce

MyModel.objects.annotate(
    current_name=Coalesce(
        Case(
            When(nickname__exact='', then=None),
            When(nickname__isnull=False, then='nickname'),
            default=None,
            output_field=CharField()
        ),
        Case(
            When(name__exact='', then=None),
            When(name__isnull=False, then='name'),
            default=None,
            output_field=CharField()
    ))).order_by('current_name')