Django Order By Date, but have “None” at end?

Question

I have a model of work orders, with a field for when the work order is required by. To get a list of work orders, with those that are required early, I do this:

wo = Work_Order.objects.order_by('dateWORequired')

This works nicely, but ONLY if there is actually a value in that field. If there is no required date, then the value is None. Then, the list of work orders has all the None's at the top, and then the remaining work orders in proper order.

How can I get the None's at the bottom?

Answer 1

q = q.extra(select={
        'date_is_null': 'dateWORequired IS NULL',
    },
    order_by=['date_is_null','dateWORequired'],
)

You might need a - before the date_is_null in the order_by portion, but that's how you can control the behavior.

Answer 2

I endeavoured to get this working with pure Django, without dropping into SQL.

The F() expression function can be used with order_by, so I tried to concoct a way of creating an expression which sets all numbers to the same value, but which sets all NULLs to another specific value.

MySQL will order NULLs before 0s in ascending order, and vice versa in descending order.

So this works:

order_by( (0 * F('field')).asc() ) # Nulls first
# or:
order_by( (0 * F('field')).desc() ) # Nulls last

You can then pass any other fields to the same order_by call, before or after that expression.

I've tried it with dates and the same happens. e.g.:

SELECT 0*CURRENT_TIMESTAMP;

Evaluates to 0.

Answer 3

This was 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
long_ago = datetime.datetime(year=1980, month=1, day=1)
Work_Order.objects.order_by('dateWORequired')
MyModel.objects.annotate(date_null=
    Coalesce('dateWORequired', Value(long_ago))).order_by('date_null')

Coalesce selects the first non-null value, so you create a value date_null to order by which is just dateWORequired but with null replaced by a date long ago.

Answer 4

Requirements: Python 3.4, Django 10.2, PostgreSQL 9.5.4

Variant 1

Solution:

class IsNull(models.Func):

    template = "%(expressions)s IS NULL"

Usage (None always latest):

In [1]: a = User.polls_manager.users_as_voters()

In [4]: from django.db import models

In [5]: class IsNull(models.Func):
   ...:     template = "%(expressions)s IS NULL"
   ...:     

In [7]: a = a.annotate(date_latest_voting_isnull=IsNull('date_latest_voting'))

In [9]: for i in a.order_by('date_latest_voting_isnull', 'date_latest_voting'):
   ...:     print(i.date_latest_voting)
   ...:     
2016-07-30 01:48:11.872911+00:00
2016-08-31 13:13:47.240085+00:00
2016-09-16 00:04:23.042142+00:00
2016-09-18 19:45:54.958573+00:00
2016-09-26 07:27:34.301295+00:00
2016-10-03 14:01:08.377417+00:00
2016-10-21 16:07:42.881526+00:00
2016-10-23 11:10:02.342791+00:00
2016-10-31 04:09:03.726765+00:00
None

In [10]: for i in a.order_by('date_latest_voting_isnull', '-date_latest_voting'):
    ...:     print(i.date_latest_voting)
    ...:     
2016-10-31 04:09:03.726765+00:00
2016-10-23 11:10:02.342791+00:00
2016-10-21 16:07:42.881526+00:00
2016-10-03 14:01:08.377417+00:00
2016-09-26 07:27:34.301295+00:00
2016-09-18 19:45:54.958573+00:00
2016-09-16 00:04:23.042142+00:00
2016-08-31 13:13:47.240085+00:00
2016-07-30 01:48:11.872911+00:00
None

Notes

1. Based on https://www.isotoma.com/blog/2015/11/23/sorting-querysets-with-nulls-in-django/
2. Drawback: unnecessary buffer field, overhead for ordering

Variant 2

Solution:

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


class NullsLastCompiler(SQLCompiler):

    # source code https://github.com/django/django/blob/master/django/db/models/sql/compiler.py

    def get_order_by(self):

        result = super(NullsLastCompiler, self).get_order_by()

        # if result exists and backend is PostgreSQl
        if result and self.connection.vendor == 'postgresql':

            # modified raw SQL code to ending on NULLS LAST after ORDER BY
            # more info https://www.postgresql.org/docs/9.5/static/queries-order.html
            result = [
                (expression, (sql + ' NULLS LAST', params, is_ref))
                for expression, (sql, params, is_ref) in result
            ]

        return result


class NullsLastQuery(models.sql.Query):

    # source code https://github.com/django/django/blob/master/django/db/models/sql/query.py
    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 own compiler
        return NullsLastCompiler(self, connection, using)


class NullsLastQuerySet(models.QuerySet):

    # source code https://github.com/django/django/blob/master/django/db/models/query.py
    def __init__(self, model=None, query=None, using=None, hints=None):

        super(NullsLastQuerySet, self).__init__(model, query, using, hints)

        # replace on own Query
        self.query = query or NullsLastQuery(model)

Usage:

# instead of models.QuerySet use NullsLastQuerySet
class UserQuestionQuerySet(NullsLastQuerySet):

    def users_with_date_latest_question(self):

        return self.annotate(date_latest_question=models.Max('questions__created'))


#connect to a model as a manager
class User(AbstractBaseUser, PermissionsMixin):
    .....

    questions_manager = UserQuestionQuerySet().as_manager()

Results (None always latest):

In [2]: qs = User.questions_manager.users_with_date_latest_question()

In [3]: for i in qs:
   ...:     print(i.date_latest_question)
   ...:     
None
None
None
2016-10-28 20:48:49.005593+00:00
2016-10-04 19:01:38.820993+00:00
2016-09-26 00:35:07.839646+00:00
None
2016-07-27 04:33:58.508083+00:00
2016-09-14 10:40:44.660677+00:00
None

In [4]: for i in qs.order_by('date_latest_question'):
   ...:     print(i.date_latest_question)
   ...:     
2016-07-27 04:33:58.508083+00:00
2016-09-14 10:40:44.660677+00:00
2016-09-26 00:35:07.839646+00:00
2016-10-04 19:01:38.820993+00:00
2016-10-28 20:48:49.005593+00:00
None
None
None
None
None

In [5]: for i in qs.order_by('-date_latest_question'):
   ...:     print(i.date_latest_question)
   ...:     
2016-10-28 20:48:49.005593+00:00
2016-10-04 19:01:38.820993+00:00
2016-09-26 00:35:07.839646+00:00
2016-09-14 10:40:44.660677+00:00
2016-07-27 04:33:58.508083+00:00
None
None
None
None
None

Notes:

1. Based on the Django: Adding "NULLS LAST" to query and the Django`s source code

2. Global on all fields of a model (it is advantage and disadvantage simultaneously)

3. No a unnecessary field

4. A drawback - tested only on the PostgreSQL

Answer 5

Django 1.11 added this as a native feature. It's a little convoluted. It is documented.

Ordered with only one field, ascending:

wo = Work_Order.objects.order_by(F('dateWORequired').asc(nulls_last=True)

Ordered using two fields, both descending:

wo = Work_Order.objects.order_by(F('dateWORequired').desc(nulls_last=True), F('anotherfield').desc(nulls_last=True))