Django: ordering numerical value with order_by

Question

I'm in a situation where I must output a quite large list of objects by a CharField used to store street addresses.

My problem is, that obviously the data is ordered by ASCII codes since it's a Charfield, with the predictable results .. it sort the numbers like this;

1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21.... 

Now the obvious step would be to change the Charfield the proper field type (IntegerField let's say), however it cannot work since some address might have apartments .. like "128A".

I really don't know how I can order this properly ..

Answer 1

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') 

Answer 2

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