Code

Opened 4 years ago

Last modified 3 years ago

#13006 new New feature

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: gav, 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.

Attachments (0)

Change History (10)

comment:1 Changed 4 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

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 follow-up: Changed 4 years ago by 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.

comment:3 in reply to: ↑ 2 Changed 4 years ago by russellm

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 Changed 4 years ago by 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 :)

comment:5 Changed 4 years ago by gav

  • Cc gav added

comment:6 Changed 3 years ago by guettli

  • Cc hv@… added

comment:7 Changed 3 years ago by lukeplant

  • Type set to New feature

comment:8 Changed 3 years ago by lukeplant

  • Severity set to Normal

comment:9 Changed 2 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:10 Changed 2 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from nobody to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.