Django: Adding "NULLS LAST" to query

Step by step guide

Coercing "NULLS LAST" in older Django versions

In the event where you're using an antiquated version of Django, say, prior to 1.11, you can still mimic the intended NULLS LAST functionality using annotations in your query. You can employ database functions like Coalesce along with Value to conveniently replace nulls with a specific value which would position nulls at the end when sorting:

from django.db.models import Value, IntegerField
from django.db.models.functions import Coalesce

# Annotating null fields with party poopers, pushing them to the end
queryset = MyModel.objects.annotate(
    my_field_order=Coalesce('my_field', Value(999999))  # proclaiming a 999999 party!
).order_by('my_field_order')


In this case, we safely assume that my_field contains numerical elements. We annotate nulls with a wilfully high-fixed value, forcing nulls to party at the end (commonly known as the 999999 party).

Homegrown solutions for custom needs

If the regular course of action doesn't make the cut, fear not. There's enough room for customization in Django. You can think about setting up a NullsLastSQLCompiler or a NullsLastManager for your models. This intuitive move not only locks up your "NULLS LAST" logic for future use but also proves handy when dealing with convoluted database backends demanding meticulous control over the resulting SQL.

Performance: A critical checkpoint

Amidst this customization, don't forget to pay attention to your database performance. The select_related() and prefetch_related() come in handy when dealing with linked objects. Ensure to use these methods efficiently to minimize database hits and reduce overall latency.

Debugging and specific database know-hows

Constructing custom queries mandates you to inspect logs for better understanding of the resulting SQL queries. This habit will shine light on both query debugging and specific nuances of executing queries across different databases. While PostgreSQL graciously supports NULLS LAST in query statements, other databases may demand additional custom SQL or functions.

Bonus sort ordering tip

When your data demands multiple sorting levels, consider ordering populated fields first before appending the nulls_last flag:

# Double sorting action with NULLS LAST
queryset = MyModel.objects.order_by('populated_field', F('my_field').desc(nulls_last=True))


This guarantees a logical order sequence, first grouping data by a populated_field, then applying "NULLS LAST" principle on my_field.

Say 'No' to the extra() method

While scouring through age-old methods, you might stumble upon extra() for manipulating "NULLS LAST". But be warned! It's best avoided due to lurking threats of SQL injection attacks. Sticking with built-in ORM functionalities or their safe extended use is the right way to go.