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?

score 58 · Accepted Answer · answered Mar 08 '11 at 17:55

58

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

answered Mar 08 '11 at 17:55

Sergey Golovchenko

18,203
15
55
72

Somehow this does not work for me, maybe because I have as an ordering field: position__name (which is position.name). Too bad. Ah, wait! I renamed the null_position to null and it seems django does not like that! Working now, thanks! :) – gabn88 Sep 14 '15 at 17:27
Performance won't be good since it becomes an aggregate query. – Harry Jun 25 '17 at 16:20
Django 1.11 builds in proper support: https://stackoverflow.com/a/44731168/237091 – Scott Stafford Jun 05 '18 at 16:57

score 21 · Answer 2 · edited Feb 16 '17 at 09:09

21

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

edited Feb 16 '17 at 09:09

Gabriel

1,870
1
19
20

answered May 11 '15 at 07:04

shredding

5,374
3
46
77

7

thanks for that link -- here's how I used `Coalesce` on datetime attributes: https://gist.github.com/yosemitebandit/ec3adc02d927375f13d0 – Matt Jun 23 '15 at 00:19
1

@Matt's code should be added to the answer - it makes it twice as useful – LostMyGlasses Nov 24 '16 at 11:51
I added @Matts sample to my answer, so do don't forget to update both of us :-) – shredding Nov 25 '16 at 15:37
There is a problem with this in which it says unicode is missing tzinfo. – Zargold Feb 16 '17 at 20:42
it's a shame Coalesce doesn't also work for empty strings – gdvalderrama Feb 23 '17 at 11:20
In newest Django versions `Value` can be found directly from `django.db.models`. – Raekkeri Aug 22 '17 at 11:10

score 14 · Answer 3 · answered May 17 '18 at 13:19

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.

score 9 · Answer 4 · edited Jul 23 '15 at 14:12

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.

score 3 · Answer 5 · answered Feb 18 '15 at 16:11

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

score 1 · Answer 6 · answered Mar 08 '11 at 16:27

1

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

answered Mar 08 '11 at 16:27

Ignacio Vazquez-Abrams

776,304
153
1,341
1,358

It is deprecated by now. – blueyed Feb 19 '16 at 00:12
`.extra()` is not deprecated. But it may be in the future. – Simon Steinberger Jun 28 '16 at 07:29

Django: order by position ignoring NULL

6 Answers6

Linked