Django: ordering numerical value with order_by

If you're sure there are only integers in the field, you could get the database to cast it as an integer via the extra method, and order by that:

MyModel.objects.extra(
    select={'myinteger': 'CAST(mycharfield AS INTEGER)'}
).order_by('myinteger')

Django is trying to deprecate the extra() method, but has introduced Cast() in v1.10. In sqlite (at least), CAST can take a value such as 10a and will cast it to the integer 10, so you can do:

from django.db.models import IntegerField
from django.db.models.functions import Cast

MyModel.objects.annotate(
    my_integer_field=Cast('my_char_field', IntegerField())
).order_by('my_integer_field', 'my_char_field')

which will return objects sorted by the street number first numerically, then alphabetically, e.g. ...14, 15a, 15b, 16, 16a, 17...

Tags:

Sorting

Django