django-orm case-insensitive order by

Question

I know, I can run a case insensitive search from DJango ORM. Like,

User.objects.filter(first_name__contains="jake")
User.objects.filter(first_name__contains="sulley")
User.objects.filter(first_name__icontains="Jake")
User.objects.filter(first_name__icontains="Sulley")

And also, I can fetch them as

user_list = User.objects.all().order_by("first_name")
# sequence: (Jake, Sulley, jake, sulley)
user_list = User.objects.all().order_by("-first_name") # for reverse
# sequence: (sulley, jake, Sulley, Jake)

Is there a direct way for a case-insensitive fetch?? As in I want a sequence as

# desired sequence: jake, Jake, sulley, Sulley

If not, then suggest a best way to do it. Thanks in advance.

This is for postgresql, but maybe it will be useful for other databases too: http://scottbarnham.com/blog/2007/11/20/case-insensitive-ordering-with-django-and-postgresql/ — gruszczy, Aug 04 '10 at 19:37

score 115 · Answer 1 · edited May 02 '16 at 09:11

115

Since Django 1.8 it is possible with:

from django.db.models.functions import Lower
MyModel.objects.order_by(Lower('myfield'))

https://code.djangoproject.com/ticket/6498

edited May 02 '16 at 09:11

answered Dec 29 '15 at 07:27

uri.z

1,410
2
10
9

Is it possible to use Lower with multiple columns? I can only seem to get it to work with one column. – Ben Jun 15 '17 at 23:57
2

It is possible: User.objects.order_by(Lower('username'), Lower('email')) – uri.z Jun 19 '17 at 07:50
models.py class Meta: ordering = [Lower('name')] – Alex Winkler Sep 21 '20 at 07:30
3

for descending order use: MyModel.objects.order_by(Lower('myfield').desc()) – Dre Mar 08 '21 at 20:08

score 25 · Accepted Answer · edited Oct 13 '21 at 20:40

25

This answer is outdated, follow top voted solution with django >= 1.8

I found solution using .extra

class MyModelName(models.Model):
   is_mine = models.BooleanField(default=False)
   name = models.CharField(max_length=100)


MyModelName.objects.filter( is_mine=1 ).extra(\
    select={'lower_name':'lower(name)'}).order_by('lower_name')

original link:

http://naorrosenberg.blogspot.fi/2011/04/django-models-orderby-charfield-case.html

edited Oct 13 '21 at 20:40

Phil Dukhov

67,741
15
184
220

answered Aug 19 '12 at 11:54

Troyhy

458
5
5

1

This won't work when traversing relationship, i.e "name__something" , however you can make it works with the Lower function since django 1.8 : 'from django.db.models.functions import Lower' and 'MyModelName.objects.order_by(Lower('name_something'))' here it is https://docs.djangoproject.com/en/1.9/_modules/django/db/models/functions/#Lower – romainm Feb 26 '16 at 16:35

score 2 · Answer 3 · answered May 16 '21 at 16:29

Lets take an example where you have to do a case insensitive order by of the field "first_name" from the User Model

# Importing the Lower Function
from django.db.models.functions import Lower


#For ordering by Ascending order
User.objects.all().order_by(Lower('first_name'))

#For ordering by Descending order
User.objects.all().order_by(Lower('first_name').desc())

The Lower function converts all the "first_name" values into lowercase, and then it's ordered accordingly.

django-orm case-insensitive order by

3 Answers3

Linked

Related