How to sort by presence of value or null with Django ORM

user_1216183 0
Viewed 196

I want to sort a table by two columns. For the first column, I want to sort by the presence of a value or NULL.

Unworking code to illustrate what I want to do :

MyModel.objects.order_by('blop IS NULL', 'name')

I've tried to use annotate but didn't find a way to define an expression to setting a field, or an aggregation function to return a boolean from a field.

Unworking code to illustrate what I tried:

MyModel.objects.annotate(has_blop=F('blop IS NULL')).order_by('has_blop', 'name')

I think i could manage using conditional annotation (Case... When...) but it seems overkill.

The database used is postgres.

django-orm django
1 Answers

You can order by the condition Q(blop=None). It however requires an ExpressionWrapper [Django-doc] to convert the condition to a boolean field:

from django.db.models import BooleanField, ExpressionWrapper, Q

MyModel.objects.order_by(
    ExpressionWrapper(Q(blop=None), BooleanField()).asc(),
    'name'
)
user_675790
Related