Queryset sorting: Specifying column collation for

问题:

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

回答1:

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().

回答2:

allright. It seems that right now the Raw Queries are the only way to do this.

But There is django ticket open which will hopefully be closed/resolved sometime soon.