Opened 14 years ago

Closed 9 years ago

#13006 closed New feature (fixed)

Add order_by with sql LOWER() support

Reported by: Raydiation Owned by: nobody
Component: Database layer (models, ORM) Version: 1.1
Severity: Normal Keywords: sql, order, lower
Cc: George Vilches, hv@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (11)

comment:1 by Russell Keith-Magee, 14 years ago

Triage Stage: UnreviewedAccepted

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)

comment:2 by Raydiation, 14 years ago

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.

in reply to:  2 comment:3 by Russell Keith-Magee, 14 years ago

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.

comment:4 by Raydiation, 14 years ago

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 :)

comment:5 by George Vilches, 14 years ago

Cc: George Vilches added

comment:6 by Thomas Güttler, 13 years ago

Cc: hv@… added

comment:7 by Luke Plant, 13 years ago

Type: New feature

comment:8 by Luke Plant, 13 years ago

Severity: Normal

comment:9 by Aymeric Augustin, 12 years ago

UI/UX: unset

Change UI/UX from NULL to False.

comment:10 by Aymeric Augustin, 12 years ago

Easy pickings: unset

Change Easy pickings from NULL to False.

comment:11 by Tim Graham, 9 years ago

Resolution: fixed
Status: newclosed

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

Note: See TracTickets for help on using tickets.
Back to Top