Add order_by with sql LOWER() support

[Raydiation]

I have to order a huge list of of names and when i order by the the title of an artist, uppercase A comes after lowercase z, which i dont want. To fix it i currently order with the extra() method which is very inconveniant. I thought of something like:

result = MusicCollection.objects.all().order_by(artists__lower, songs__lower)

where

__lower

triggers the sql LOWER() function on the column.

This would take less effort than to use the long extra statement every time.

[Russell Keith-Magee]

Yes to the use case, no to the proposed API. A better approach would be a variation on #10972: allow annotation of modified columns in a query, and then allow ordering on those columns - for example:

MusicCollection.objects.annotate(lc_artist_name=LowerCase('artist__name')).order_by(lc_artist_name)

[Raydiation]

Hm, thats about the same as the method with extra.
Imagine youd have to order 4 columns:

result = MusicCollection.objects.all().order_by(artists__lower, songs__lower, genre__lower, album__lower)

vs.

result = MusicCollection.objects.annotate(lcartist=LowerCase('artist__name'), lcsongs=LowerCase('songs__name'), lcgenre=LowerCase('genre__name'), lcalbum=LowerCase('artist__album')).order_by(lcartist, lcsongs, lcgenre, lcalbum)

vs.

result = MusicCollection.objects.all().extra(select={'lcartist': 'lower(artist)', 'lcsongs': 'lower(song)', 'lcgenre': 'lower(genre)', 'lalbum': 'lower(album)'}).order_by('lcartist', "lcsongs", "lcgenre", "lcalbum")

You see, annotate can take a wide spectrum of stuff, but it doesnt specifically tackle this common issue. Imho its about to write as little code as it needs, and the annotate version is even more to write than the extra version. I wouldnt call this an improvement.

[Russell Keith-Magee]

Replying to Raydiation:

Imho its about to write as little code as it needs

Brevity is a nice side effect when possible, but clarity and flexibility is the real goal we are aiming at.

__lower suffers from several problems:

• The literal python syntax you are proposing isn't actually valid python - you would need to pass the artists__lower as a string.
• Ignoring the syntax problem, it's inconsistent with current usage of the double underscore. Current usage is to define a comparison operator, not to define a transformation.
• It's not easy for users to define their own operators.

As another possible approach, I'd be much more likely to support:

result = MusicCollection.objects.all().order_by(LowerCase('artist__name'), LowerCase('songs__name'), LowerCase('genre__name'))

This is will ultimately require the same internal code, but avoids the need to explicitly name the annotations.

[Raydiation]

result = MusicCollection.objects.all().order_by(LowerCase('artist__name'), LowerCase('songs__name'), LowerCase('genre__name'))

This seems like a very good solution too me, im happy with this :)

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Aymeric Augustin]

Change Easy pickings from NULL to False.

[Tim Graham]

This can be done using the ​query expressions API in Django 1.8.