Opened 12 years ago

Closed 5 years ago

Last modified 3 years ago

#6498 closed New feature (fixed)

Add case insensitive model ordering

Reported by: Bastian Kleineidam <calvin@…> Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: gav@…, mmitar@…, Garry Polley Triage Stage: Accepted
Has patch: yes Needs documentation: yes
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Ramiro Morales)

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

Attachments (1)

0045-Add-case-insensitive-model-ordering.patch (4.9 KB) - added by Bastian Kleineidam <calvin@…> 12 years ago.

Download all attachments as: .zip

Change History (26)

Changed 12 years ago by Bastian Kleineidam <calvin@…>

comment:1 Changed 12 years ago by Pete Crosier

Needs documentation: set

comment:2 Changed 12 years ago by Jacob

Triage Stage: UnreviewedDesign decision needed

comment:3 Changed 12 years ago by Ramiro Morales

Description: modified (diff)

comment:4 Changed 12 years ago by George Vilches

Cc: gav@… added

comment:5 Changed 12 years ago by anonymous

milestone: post-1.0

comment:6 Changed 11 years ago by (none)

milestone: post-1.0

Milestone post-1.0 deleted

comment:7 Changed 11 years ago by Thejaswi Puthraya

Component: UncategorizedDatabase layer (models, ORM)

comment:8 Changed 9 years ago by Julien Phalip

Type: New feature

comment:9 Changed 9 years ago by Julien Phalip

Severity: Normal

comment:10 Changed 9 years ago by Alex Gaynor

Easy pickings: unset
Triage Stage: Design decision neededAccepted
UI/UX: unset

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

comment:11 Changed 9 years ago by 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.

comment:12 Changed 8 years ago by Mitar

Cc: mmitar@… added

comment:13 Changed 7 years ago by anonymous

¿What about a parameter like 'iexact'?

comment:14 Changed 7 years ago by Garry Polley

Any reason this can't just work like this:


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.

Last edited 7 years ago by Garry Polley (previous) (diff)

comment:15 Changed 7 years ago by 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


This would also include a doc update about the change.

comment:16 Changed 7 years ago by Garry Polley

I'd expect this python


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.

comment:17 Changed 7 years ago by 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.)

comment:18 Changed 7 years ago by Anssi Kääriäinen

If custom lookups supports gets added, doing this for your project will be much easier. See for an example.

Of course, there is that if above...

comment:19 Changed 7 years ago by Garry Polley

Cc: Garry Polley added

comment:20 Changed 7 years ago by 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.

comment:21 in reply to:  20 Changed 6 years ago by anonymous

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

comment:22 Changed 6 years ago by 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).

comment:23 Changed 5 years ago by Malte Gerth

Any news about case insensitive ordering?

comment:24 Changed 5 years ago by Tim Graham

Resolution: fixed
Status: newclosed

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

comment:25 Changed 3 years ago by 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.

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