[FIXED] Queryset sorting: Specifying column collation for django ORM query

Issue

I started investigating why my Django Model.objects.filter(condition = variable).order_by(textcolumn) queries do not yield objects in correct order. And found out that it is database (Postgresql) issue.

In my earlier question (Postgresql sorting language specific characters (collation)) i figured out (with a lot of help from zero323 in actually getting it to work) that i can specify collation per database query like this:

SELECT nimi COLLATE "et_EE" FROM test ORDER BY nimi ASC;

But as much as i can see, order_by only accepts field names as arguments.

I was wondering, that if it is somehow possible to extend that functionality to include also the collation parameter? Is it possible to hack it in somehow using mixins or whatnot? Or is feature request the only way to do this right now?

I wish it would work something like this:

Model.objects.filter(condition = variable).order_by(*fieldnames, collation = 'et_EE')

Edit1: Apparently im not the only one to ask for this: https://groups.google.com/forum/#!msg/django-developers/0iESVnawNAY/JefMfAm7nQMJ

Alan

Solution

As @olau menioned in the comment, since Django 3.2 Collate utility is available. For older Django versions see the original information below the following code sample:

# Starting with Django 3.2:
from django.db.models.functions import Collate
Test.objects.order_by(Collate('nimi', 'et_EE'))

Since Django 1.8 order_by() accepts not only field names but also query expressions.

In another answer I gave an example of how you can override the default collation for a column. The useful query expression here is Func(), which you may subclass or use directly:

nimi_et = Func(
    'nimi',
    function='et_EE',
    template='(%(expressions)s) COLLATE "%(function)s"')
Test.objects.order_by(nimi_et.asc())

Yet, note that the resulting SQL will be more like:

SELECT nimi FROM test ORDER BY nimi COLLATE "et_EE" ASC;

That is, the collation is overridden in ORDER BY clause rather than in SELECT clause. However, if you need to use it in a WHERE clause, you can use Func() in annotate().

Answered By - Piotr Ćwiek