Opened 16 years ago

Closed 9 years ago

Last modified 6 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: dev
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@…> 16 years ago.

Download all attachments as: .zip

Change History (26)

by Bastian Kleineidam <calvin@…>, 16 years ago

comment:1 by Pete Crosier, 16 years ago

Needs documentation: set

comment:2 by Jacob, 16 years ago

Triage Stage: UnreviewedDesign decision needed

comment:3 by Ramiro Morales, 16 years ago

Description: modified (diff)

comment:4 by George Vilches, 16 years ago

Cc: gav@… added

comment:5 by anonymous, 16 years ago

milestone: post-1.0

comment:6 by (none), 15 years ago

milestone: post-1.0

Milestone post-1.0 deleted

comment:7 by Thejaswi Puthraya, 15 years ago

Component: UncategorizedDatabase layer (models, ORM)

comment:8 by Julien Phalip, 13 years ago

Type: New feature

comment:9 by Julien Phalip, 13 years ago

Severity: Normal

comment:10 by Alex Gaynor, 13 years ago

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 by Alex Gaynor, 13 years ago

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 by Mitar, 12 years ago

Cc: mmitar@… added

comment:13 by anonymous, 11 years ago

¿What about a parameter like 'iexact'?

comment:14 by Garry Polley, 11 years ago

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.

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

comment:15 by Garry Polley, 11 years ago

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.

comment:16 by Garry Polley, 11 years ago

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.

comment:17 by Aymeric Augustin, 11 years ago

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 by Anssi Kääriäinen, 11 years ago

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...

comment:19 by Garry Polley, 11 years ago

Cc: Garry Polley added

comment:20 by Shai Berger, 11 years ago

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.

in reply to:  20 comment:21 by anonymous, 10 years ago

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

comment:22 by Marc Tamlyn, 10 years ago

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 by Malte Gerth, 9 years ago

Any news about case insensitive ordering?

comment:24 by Tim Graham, 9 years ago

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 by Pascal Polleunus, 6 years ago

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