Imagine you have something like this in Django:


class MyModel(models.Models):
    last_name = models.CharField(max_length=255, blank=True)
    ...

The most basic sorting is either: queryset.order_by('last_name') or queryset.order_by('-last_name'). But what if you want entries with a blank string last? And, you want it to be case insensitive. Here's how you do it:


from django.db.models.functions import Lower, NullIf
from django.db.models import Value


if reverse:
    order_by = Lower("last_name").desc()
else:
    order_by = Lower(NullIf("last_name", Value("")), nulls_last=True)


ALL = list(queryset.values_list("last_name", flat=True))
print("FIRST 5:", ALL[:5])
# Will print either...
#   FIRST 5: ['Zuniga', 'Zukauskas', 'Zuccala', 'Zoller', 'ZM']
# or 
#   FIRST 5: ['A', 'aaa', 'Abrams', 'Abro', 'Absher']
print("LAST 5:", ALL[-5:])
# Will print...
#   LAST 5: ['', '', '', '', '']

This is only tested with PostgreSQL but it works nicely.
If you're curious about what the SQL becomes, it's:


SELECT "main_contact"."last_name" FROM "main_contact" 
ORDER BY LOWER(NULLIF("main_contact"."last_name", '')) ASC

or


SELECT "main_contact"."last_name" FROM "main_contact" 
ORDER BY LOWER("main_contact"."last_name") DESC

Note that if your table columns is either a string, an empty string, or null, the reverse needs to be: Lower("last_name", nulls_last=True).desc().

Comments

Jannis

Great solution. Thank you for posting this.

I also needed to have German umlauts in their natural order (i.e. `ä` before `b` and not after `z`) so I ended up with this:

Model.objects.order_by(Collate(Lower(NullIf("my_field", Value(""))), "de-x-icu"))

Your email will never ever be published.

Previous:
How to close a HTTP GET request in Python before the end March 30, 2022 Python
Next:
Auto-merge GitHub pull requests based on "partial required checks" May 3, 2022 GitHub
Related by category:
A Python dict that can report which keys you did not use June 12, 2025 Python
Faster way to sum an integer series in Python August 28, 2025 Python
Combining Django signals with in-memory LRU cache August 9, 2025 Django, Python
Native connection pooling in Django 5 with PostgreSQL June 25, 2025 Django, Python
Related by keyword:
Find the largest node_modules directories with bash September 30, 2022 Linux, Bash, macOS
How to count the most common lines in a file October 7, 2022 Linux, Bash, macOS
Sort a JavaScript array by some boolean operation December 2, 2021 JavaScript
In Python you sort with a tuple June 14, 2013 Python