Sort Queryset: specifying column sorting for django ORM query
I started researching why my Django Model.objects.filter (condition = variable) .order_by (textcolumn) queries were not giving the objects in the correct order. And found out that this is a database problem (Postgresql).
In my previous question ( Postgresql sorting specific characters (sorting) ), I realized (with a lot of help from zero323 in reality to get it to work) that I can specify a sort for every database query like this:
SELECT nimi COLLATE "et_EE" FROM test ORDER BY nimi ASC;
But as far as I can see, order_by only takes field names as arguments.
I was wondering what if it is possible to somehow extend this functionality to include the mapping option as well? Is it possible to hack it in some way with mixins or something else? Or is feature request the only way to do it right now?
I would like it to work something like this:
Model.objects.filter(condition = variable).order_by(*fieldnames, collation = 'et_EE')
Edit1: Apparently I'm not the only one asking for this: https://groups.google.com/forum/#!msg/django-developers/0iESVnawNAY/JefMfAm7nQMJ
Alan
source to share
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. A useful query expression here is Func () , which you can subclass or use directly:
nimi_et = Func(
'nimi',
function='et_EE',
template='(%(expressions)s) COLLATE "%(function)s"')
Test.objects.order_by(nimi_et.asc())
Note, however, that the resulting SQL will look more like:
SELECT nimi FROM test ORDER BY nimi COLLATE "et_EE" ASC;
That is, the mapping is overridden in ORDER BY
, not in SELECT
. However, if you need to use it in a sentence WHERE
, you can use it Func()
in annotate()
.
source to share