Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django: Adding "NULLS LAST" to query

Tags:

python

sql

django

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"

like image 692
GabiMe Avatar asked Feb 27 '13 19:02

GabiMe


8 Answers

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

like image 197
kabucey Avatar answered Sep 28 '22 08:09

kabucey


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.

like image 28
Mariano Avatar answered Sep 28 '22 06:09

Mariano


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()
like image 29
Tim Babych Avatar answered Sep 28 '22 08:09

Tim Babych


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

like image 42
Mark Avatar answered Sep 28 '22 06:09

Mark


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.

like image 29
blueyed Avatar answered Sep 28 '22 06:09

blueyed


@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.

like image 30
Edward D'Souza Avatar answered Sep 28 '22 07:09

Edward D'Souza


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)
like image 33
stdword Avatar answered Sep 28 '22 06:09

stdword


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...
like image 40
Kevin Parker Avatar answered Sep 28 '22 06:09

Kevin Parker