Monday, April 18, 2011

Django models - order_by CharField case insensitive

Working with Django models, I needed to sort my data by a string (CharField) column:

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

So I used this python code:
MyModelName.objects.filter( is_mine=1 ).order_by('name')

However, the default sorting is case sensitive, which caused odd results order:

A
B
C
a
b
c

The solution was to normalize the data (change to lowercase) and then sort:
MyModelName.objects.filter( is_mine=1 ).extra( select={'lower_name': 'lower(name)'}).order_by('lower_name')

So now I get this result:
A
a
B
b
C
c

Which is exactly what I need !! :-)

More on Django API "extra" function here.

2 comments:

Franz said...

Thanks. I was struggeling with this for hours. I used random order instead, but the client was not to happy about that solution.

Naor Rosenberg said...

Great! I'm glad you found my post useful.