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...