Django: Adding "NULLS LAST" to query

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

This functionality has been added to Django 1.11.

https://docs.djangoproject.com/en/dev/releases/1.11/

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

Reference for Django 3.1: https://docs.djangoproject.com/en/3.1/ref/models/expressions/#using-f-to-sort-null-values

Closest thing I've found is doing it on two steps. First ordering on the populated field and then on the nulls:

Via this gist (itself via these django logs):

all_projects = Project.objects.select_related().filter(
    company=company).order_by('-date_due')

q = all_projects.extra(select={'date_due_null': 'date_due is null'})
q = q.extra(order_by=['date_due_null'])
print q.query

Caution: note the warnings regarding extra(), and that it may be deprecated in the future.

If you want it to be done transparently and on all columns, you can redefine sql generation. To do so, you would need to have your own Manager to return your custom QuerySet to return your custom Query to use custom Compiler. My code for that looks like that (Django 1.5):

from django.db import models, connections

class NullsLastQuery(models.sql.query.Query):
    """
    Query that uses custom compiler,
    to utilize PostgreSQL feature of setting position of NULL records
    """
    def get_compiler(self, using=None, connection=None):
        if using is None and connection is None:
            raise ValueError("Need either using or connection")
        if using:
            connection = connections[using]

        # defining that class elsewhere results in import errors
        from django.db.models.sql.compiler import SQLCompiler
        class NullsLastSQLCompiler(SQLCompiler):
            def get_ordering(self):
                result, group_by = super(NullsLastSQLCompiler, self
                    ).get_ordering()
                if self.connection.vendor == 'postgresql' and result:
                    result = [line + " NULLS LAST" for line in result]
                return result, group_by

        return NullsLastSQLCompiler(self, connection, using)

class NullsLastQuerySet(models.query.QuerySet):
    def __init__(self, model=None, query=None, using=None):
        super(NullsLastQuerySet, self).__init__(model, query, using)
        self.query = query or NullsLastQuery(self.model)

class NullsLastManager(models.Manager):
    def get_query_set(self):
        return NullsLastQuerySet(self.model, using=self._db)

class YourModel(models.Model):
    objects = NullsLastManager()

This was probably not available when the question was asked, but since Django 1.8 I think this is the best solution:

from django.db.models import Coalesce, Value
MyModel.objects.all().annotate(price_null=
    Coalesce('price', Value(-100000000)).order_by('-price_null')

Coalesce selects the first non-null value, so you create a value price_null to order by which is just price but with null replaced by -100000000 (or +?).

Django: Adding "NULLS LAST" to query

Related

Recent Posts