[Django]-Django – queryset order_by field that has characters and integers

Queryset ordering is handled by the database backend, not by Django. This limits the options for changing the way that ordering is done. You can either load all of the data and sort it with Python, or add additional options to your query to have the database use some kind of custom sorting by defining functions.

Use the queryset extra() function will allow you to do what you want by executing custom SQL for sorting, but at the expense of reduced portability.

In your example, it would probably suffice to split the input field into two sets of data, the initial character, and the remaining integer value. You could then apply a sort to both columns. Here’s an example (untested):

qs = MyModel.objects.all()

# Add in a couple of extra SELECT columns, pulling apart this_field into
# this_field_a (the character portion) and this_field_b (the integer portion).

qs = qs.extra(select={
              'this_field_a': "SUBSTR(this_field, 1)",
              'this_field_b': "CAST(substr(this_field, 2) AS UNSIGNED)"})

The extra call adds two new fields into the SELECT call. The first clause pulls out the first character of the field, the second clause converts the remainder of the field to an integer.

It should now be possible to order_by on these fields. By specifying two fields to order_by, ordering applies to the character field first, then to the integer field.

eg

qs = qs.order_by('this_field_a', 'this_field_b')

This example should work on both MySql and SQLite. It should also be possible to create a single extra field which is used only for sorting, allowing you to specify just a single field in the order_by() call.