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.

score 22 · Answer 1 · answered Apr 14 '15 at 19:45

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 '15 at 19:45

Sylvain Biehler

1,403
11
25

@Sven I would agree if my question wasn't specifically directed towards Django 1.5.3 (and not 1.8+) – Andrew Mar 04 '16 at 01:47
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) – Ben Jun 15 '17 at 22:26

score 1 · Accepted Answer · answered Dec 25 '13 at 04:18

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 '13 at 04:18

falsetru

357,413
63
732
636

If I do that, I get the following error `Cannot resolve keyword 'name_lower' into field. Choices are: accountid, active, channelid, id, name` – Andrew Dec 25 '13 at 04:21
1

@AndrewWilson, You don't need to add extra field to the model. See [this screencast](http://asciinema.org/a/6905) I just recorded. – falsetru Dec 25 '13 at 05:06
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 – Andrew Dec 25 '13 at 05:33
@AndrewWilson, Try `select name from appname_modelname order by lower(name);` in `dbshell` . Does it work? – falsetru Dec 25 '13 at 06:42
it returns the list of names without lowercasing the name in the correct order. Using Postgresql 9.3 – Andrew Dec 26 '13 at 16:49
@AndrewWilson, Is it what you want? – falsetru Dec 26 '13 at 16:51
That is what I want from the DB Query, but it's not processing that list through my Model Query – Andrew Dec 26 '13 at 16:52
@AndrewWilson, Using `Model.objects.raw('SELECT * FROM appname_modelname ORDER BY LOWER(name)')` is not an option? – falsetru Dec 26 '13 at 16:54
I believe it may be, is there any downfalls to not using the Django out of the box queries? (besides I'd get a functional response)? – Andrew Dec 26 '13 at 16:55
@AndrewWilson, SQL version is more verbose, less compatible (because of usage of `lower` function, even though it is standard SQL function). – falsetru Dec 26 '13 at 16:59
@AndrewWilson, BTW, if SQL does work, the code in the answer should also work. It's strange.. – falsetru Dec 26 '13 at 17:02
how would i reference the variable using .raw? currently I have `SELECT name, channelid, active FROM webapp_channel WHERE accountid=account ORDER BY LOWER(name)` but I don't know how to reference account as a python variable – Andrew Dec 26 '13 at 17:30
@AndrewWilson, You can pass it as parameter: `Channel.objects.raw('SELECT name, channelid, active FROM webapp_channel WHERE accountid=? ORDER BY LOWER(name)', [account])`. I'm not sure `?` is correct placeholder (Right now I don't have access to postgresql). If it does not work try with `%s`. – falsetru Dec 26 '13 at 17:32
@AndrewWilson, For more question, please post another question instead of extending comment. – falsetru Dec 26 '13 at 17:35
Postgres may be a little different on what it's requiring. Here is how I fixed it `cursor = connection.cursor() cursor.execute('SELECT name, channelid, active, id FROM webapp_channel WHERE accountid=%s ORDER BY LOWER(name)', [name]) ret cursor.fetchall()` Thank you for pointing me in the right direction. Update your answer to that so others can use it as a reference please – Andrew Dec 26 '13 at 18:23

Django Query sort case-insensitive using Model method with PostgreSQL

2 Answers2

Linked

Related