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

The problem you're up against is quite similar to how filenames get ordered when sorting by filename. There, you want "2 Foo.mp3" to appear before "12 Foo.mp3".

A common approach is to "normalize" numbers to expanding to a fixed number of digits, and then sorting based on the normalized form. That is, for purposes of sorting, "2 Foo.mp3" might expand to "0000000002 Foo.mp3".

Django won't help you here directly. You can either add a field to store the "normalized" address, and have the database order_by that, or you can do a custom sort in your view (or in a helper that your view uses) on address records before handing the list of records to a template.

Answer 2

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