6

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

Improve this question
2
  • 1
    For making suggestions on how to improve Django, file a new feature request and/or make a post on django-developers. For now you might be have to use a raw SQL query. Commented Sep 21, 2013 at 18:48
  • Hmm yes, thanks. I would/will request that feature i guess. I just want to know first, if its possible to achieve it with existing tools. Raw Query is one of them, for sure. Commented Sep 21, 2013 at 19:54

3 Answers 3

Reset to default
15

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Be aware that setting the collation in the ORDER BY prevents use of any index that would otherwise apply.
Man! This is the most important answer for i18n support in Django - and .. nobody knows, nobody cares ?? Thank you very much !
Django 3.2 shipped a Collate function that does this.
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.

Improve this answer

Comments

0

For me works:

from django.db.models.functions import Collate

Model.objects.filter(condition = variable).order_by(Collate("field_name", "C"))

Use exactly english - "C"

In the context of PostgreSQL, COLLATE "C" signifies "default" or "binary sorting." This means that strings are compared byte-by-byte, without regard for locale and sorting rules. In the "C" locale, comparisons occur at the lowest level, which can be useful in cases where the byte order within a string is important, such as when comparing binary data.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.