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:
How I run standalone Python in 2025 January 14, 2025 Python
get in JavaScript is the same as property in Python February 13, 2025 Python
How to resolve a git conflict in poetry.lock February 7, 2020 Python
Inspecting the index size in PostgreSQL April 21, 2025 PostgreSQL
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