Add case insensitive model ordering

[Bastian Kleineidam <calvin@…>]

Add support for case insensitive ordering of model objects by using
.order_by("*name") or .order_by("*-name") syntax.

[(none)]

Milestone post-1.0 deleted

[Alex Gaynor]

The idea of case insensitive ordering is accepted, some more thought should be put into the API, * for case-sensitive isn't particularly intuitive.

[Alex Gaynor]

I've closed #7580 as a dupe of this. This should be used to discuss a general way to order by alternate things.

[anonymous]

¿What about a parameter like 'iexact'?

[Garry Polley]

Any reason this can't just work like this:

my_model.objects.all().order_by('my_field__iexact')

I don't think that would cause any issues, it'll just be a bit of a chore to get it added to all the backends.

[Garry Polley]

I'm thinking of doing this for the DjangoCon sprint, will it get accepted if I do it?

My proposal is still the same

my_model.objects.all().order_by('my_field__iexact')

This would also include a doc update about the change.

[Garry Polley]

I'd expect this python

my_model.objects.all().order_by('my_field_iexact')

to become this SQL

SELECT * FROM my_model ORDER BY LOWER(my_field);  

I'm also fine using UPPER, I'm not too particular either way. Seems most people in raw SQL prefer UPPER.

[Aymeric Augustin]

I don't find my_field__iexact much better than the original proposal of '*my_field'... Try grabbing a core dev or two at the sprints to get a design decision on the API :)

Otherwise, the most difficult part is to get this working on all four databases supported by Django. Yes, this includes Oracle :) (I have the infrastructure to test it if you don't and I'll be a the sprints tomorrow.)

[Anssi Kääriäinen]

If custom lookups supports gets added, doing this for your project will be much easier. See ​https://github.com/akaariai/django/commit/89c4765044787ca8084541da0c31c2d47956c720#L1R73 for an example.

Of course, there is that if above...

[Shai Berger]

I believe my_model.objects.all().extra(select={'imf': 'UPPER(my_field)'}, order_by=['imf']) should work on all core backends.

I am not sure a dedicated API is justified.

[anonymous]

what about in case of foreign key field .... ?

[Marc Tamlyn]

A dedicated API for this is likely not justified, but we should be able to order by any Transform which can be applied to the field. At the moment, case insensitive searches are not done as a Transform, although it would be easy to create an __upper transform and use __iexact as a proxy to it (also __icontains would proxy to __upper__contains for example).

[Malte Gerth]

Any news about case insensitive ordering?

[Tim Graham]

Yes, I think it's addressed in Django 1.8 with the ability to order_by() ​expressions. For example:

>>> from django.db.models.functions import Lower
>>> MyModel.objects.order_by(Lower('myfield'))

[Pascal Polleunus]

It would still be useful to have __upper for the ordering options in models and admin.

In my ModelAdmin…

I tried ordering = [Lower("name_lower")] but it crashes as not handled.

I tried by overriding get_queryset() using qs.order_by(Lower("name_lower")).
My method is executed and returns the correct query (i.e. ORDER BY LOWER("name")) but it doesn't work in the admin (weird, the list is sorted as "-name").
That query works correctly in PostgreSQL.