django - queryset order_by field that has characte

Question

I have a model that has a field (lets call it this_field) which is stored as a string. The values in this_field are in the form Char### - as in, they have values such as: A4 or A34 or A55 (note that they will always have the same first character).

Is there a way to select all the records and order them by this field? Currently, when I do an .order_by('this_field') I get the order like this:

A1 A13 A2 A25 etc...

What I want is:

A1 A2 A13 A25 etc...

Any best approach methods or solutions to achieve this query set ordered properly?

Answer 1

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.

Answer 2

Another way of doing it is to sort the QuerySet based on the int part of this_field:

qs = ModelClass.objects.all()
sorted_qs = sorted(qs, key=lambda ModelClass: int(ModelClass.this_field[1:]))

Answer 3

If you use this sort order a lot and on bigger tables you should think about two separate fields that contain the separated values:

1. the alpha values should be lowercased only, in a text or char field, with db_index=True set
2. the numeric values should be in an integer field with db_index=True set on them.

qs.order_by('alpha_sort_field', 'numeric_sort_field')

Otherwise you will probably experience some (or up to a huge) performance impact.