Django : Order by position ignoring NULL

Question

I have a problem with django queryset ordering.

My model contains a field named position (a PositiveSmallIntegerField), which I'd like to used to order query results.

I use order_by('position'), which works great.

Problem : my position field is nullable (null=True, blank=True), because I don't wan't to specify a position for every 50000 instances of my model :(

When some instances have a NULL "position", order_by returns them in the top of the list : I'd like them to be at the end...

In RAW SQL, I used to write things like "IF(position IS NULL or position='', 1, 0)" (see http://www.shawnolson.net/a/730/mysql-sort-order-with-null.html) : is it possible to get the same result using Django, without writing raw SQL ?

Thanks very much !

Answer 1

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')

Answer 2

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

Answer 3

You can use the annotate() from django agrregation to do the trick:

items = Item.objects.all().annotate(null_position=Count('position')).order_by('-null_position', 'position')

Answer 4

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.

Answer 5

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')

Answer 6

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.