[Django]-Django: order by position ignoring NULL

As of Django 1.8 you can use Coalesce() to convert NULL to 0.

Sample:

import datetime    
from django.db.models.functions import Coalesce, Value

from app import models


# Coalesce works by taking the first non-null value.  So we give it
# a date far before any non-null values of last_active.  Then it will
# naturally sort behind instances of Box with a non-null last_active value.

the_past = datetime.datetime.now() - datetime.timedelta(days=10*365)
boxes = models.Box.objects.all().annotate(
    new_last_active=Coalesce(
        'last_active', Value(the_past)
    )
).order_by('-new_last_active')

It’s a shame there are a lot of questions like this on SO that are not marked as duplicate. See (for example) this answer for the native solution for Django 1.11 and newer. Here is a short excerpt:

Added the nulls_first and nulls_last parameters to Expression.asc() and desc() to control the ordering of null values.

Example usage (from comment to that answer):

from django.db.models import F 
MyModel.objects.all().order_by(F('price').desc(nulls_last=True))

Credit goes to the original answer author and commenter.

Using extra() as Ignacio said optimizes a lot the end query. In my aplication I’ve saved more than 500ms (that’s a lot for a query) in database processing using extra() instead of annotate()

Here is how it would look like in your case:

items = Item.objects.all().extra(
    'select': {
        'null_position': 'CASE WHEN {tablename}.position IS NULL THEN 0 ELSE 1 END'
     }
).order_by('-null_position', 'position')

{tablename} should be something like {Item’s app}_item following django’s default tables name.

I found that the syntax in Pablo’s answer needed to be updated to the following on my 1.7.1 install:

items = Item.objects.all().extra(select={'null_position': 'CASE WHEN {name of Item's table}.position IS NULL THEN 0 ELSE 1 END'}).order_by('-null_position', 'position')

QuerySet.extra() can be used to inject expressions into the query and order by them.