Django Query sort case-insensitive using Model method with PostgreSQL

Question

I'm really new to django, python and postgres... I can't seem to find the answer on how to order_by being case insensitive while using Model as the query method, only if you use direct SQL queries.

Model
@classmethod
def get_channel_list(cls, account):
    return cls.objects.filter(accountid=account).order_by('-name').values_list('name', 'channelid')

Data set and order it's currently being ordered in

test
b test
a test channel
a test channel
a test 2 
a b test
Test Channel
Test 3
Test 3
Test 2 Channel

any help would be much appreciated.

Sylvain Biehler · Accepted Answer · 2015-04-14 19:45:36Z

22

With django 1.8, there is a built-in solution:

from django.db.models.functions import Lower
...
ret = ret.order_by(Lower('name_lower'))
ret = ret.order_by(Lower('name_lower').asc())
ret = ret.order_by(Lower('name_lower').desc())

answered Apr 14, 2015 at 19:45

Sylvain Biehler

1,43311 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andrew Over a year ago

@Sven I would agree if my question wasn't specifically directed towards Django 1.5.3 (and not 1.8+)

Ben Over a year ago

This works when you're talking about sorting on just one column or multiple columns all in one direction. But, what if you need to sort multiple columns, some ascending and some descending? (e.g. I'm selecting 4 columns and I want it sorted ascending by col 1 and, secondarily, descending by col 3)

falsetru · Accepted Answer · 2013-12-25 04:18:25Z

1

Using QuerySet.extra(select=...):

@classmethod
def get_channel_list(cls, account):
    ret = cls.objects.extra(select={'name_lower': 'lower(name)'})
    ret = ret.order_by('-name_lower')
    ret = ret.filter(accountid=account).values_list('name', 'channelid')
    return channels

answered Dec 25, 2013 at 4:18

falsetru

371k69 gold badges770 silver badges660 bronze badges

15 Comments

Andrew Over a year ago

If I do that, I get the following error Cannot resolve keyword 'name_lower' into field. Choices are: accountid, active, channelid, id, name

falsetru Over a year ago

@AndrewWilson, You don't need to add extra field to the model. See this screencast I just recorded.

Andrew Over a year ago

That for some reason doesn't work for me in Django 1.5.3 and Python 2.7, not sure if it's a version thing

falsetru Over a year ago

@AndrewWilson, Try select name from appname_modelname order by lower(name); in dbshell . Does it work?

Andrew Over a year ago

it returns the list of names without lowercasing the name in the correct order. Using Postgresql 9.3

|

Collectives™ on Stack Overflow

Django Query sort case-insensitive using Model method with PostgreSQL

2 Answers 2

2 Comments

15 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

15 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related