Django: Adding "NULLS LAST" to query

Question

I would like to sort a model by using Postgresql's "NULLS LAST" option. How could it be done?

I tried something like

MyModel.objects.all().extra(order_by=('-price', 'NULLS LAST'))

But I get

"Cannot resolve keyword 'NULLS LAST' into field"

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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 +?).

Answer 5

For Django 1.9 (and possibly 1.8) you can use this:

from django.db import connections, models
from django.db.models.sql.compiler import SQLCompiler


class NullsLastSQLCompiler(SQLCompiler):
    def get_order_by(self):
        result = super().get_order_by()
        if result and self.connection.vendor == 'postgresql':
            return [(expr, (sql + ' NULLS LAST', params, is_ref))
                    for (expr, (sql, params, is_ref)) in result]
        return result


class NullsLastQuery(models.sql.query.Query):
    """Use a custom compiler to inject 'NULLS LAST' (for PostgreSQL)."""

    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]
        return NullsLastSQLCompiler(self, connection, using)


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

And then on your model(s):

objects = NullsLastQuerySet.as_manager()

This is based on the answer from Tim in https://stackoverflow.com/a/17077587/15690.

The ticket to add support for this to Django has been re-opened: https://code.djangoproject.com/ticket/13312.

Answer 6

@kabucey's answer is best for Django >= 1.11, but if you're using at least Django 1.8, 1.9 or 1.10, you can use a custom Func expression to achieve "NULLS Last" behaviour, as described at https://www.isotoma.com/blog/2015/11/23/sorting-querysets-with-nulls-in-django/:

from django.db.models import Func

class IsNull(Func):
    template = '%(expressions)s IS NULL'

MyModel.objects.all().annotate(
    price_isnull=IsNull('price_isnull'),
    ).order_by(
        'price_isnull',
        '-price',
        )

The first order_by argument sorts the list in ascending order by price_isnull, forcing null-price items to the end of the list since True > False.

Answer 7

There is an another way to add managed nulls functionality to Django < v1.11 with Django v1.11 style:

from my_project.utils.django import F
MyModel.objects.all().order_by(F('price').desc(nulls_last=True))
# or
MyModel.objects.all().order_by(F('price').desc().nullslast())

Cons:

1. Easy migration to Django 1.11
2. We don't get deep into query compiler internals

To do so we need to override django.db.models.F and django.db.models.expressions.OrderBy classes:

from django.db.models import F as DjangoF
from django.db.models.expression import OrderBy as DjangoOrderBy


class OrderBy(DjangoOrderBy):
    def __init__(self, expression, descending=False, nulls_last=None):
        super(OrderBy, self).__init__(expression, descending)
        self.nulls_last = nulls_last
    ...

    def as_sql(self, compiler, connection, template=None, **extra_context):
        ...
        ordering_value = 'DESC' if self.descending else 'ASC'
        if self.nulls_last is not None:
            nulls_value = 'LAST' if self.nulls_last else 'FIRST'
            ordering_value += ' NULLS ' + nulls_value

        placeholders = {
            'expression': expression_sql,
            'ordering': ordering_value,
        }
        ...

    def nullslast(self):
        self.nulls_last = True

    def nullsfirst(self):
        self.nulls_last = False


class F(DjangoF):
    ...

    def asc(self, nulls_last=None):
        return OrderBy(self, nulls_last=nulls_last)

    def desc(self, nulls_last=None):
        return OrderBy(self, descending=True, nulls_last=nulls_last)

Answer 8

We wanted to chain multiple order by statements, some ASC, some DESC all with NULLS LAST. There doesn't seem to be the possibility of this with order_by as it has the following call:

obj.query.clear_ordering(force_empty=False)

So you can do it with the following by appending add_ordering calls:

qs = ATeamModel.objects.filter(whatever=1)
qs.query.add_ordering(F('date_updated').desc(nulls_last=True))
qs.query.add_ordering(F('date_created').desc(nulls_last=True))

qs...