Django - order_by() char and numerical

Question

I have to sort a list of objects containing hostnames.

The hostnames are in these formats: h1, h5, h10, h12, h12-abc, h1000, x10

If i use order_by('hostname') it will order like this:

h1, h10, h1000, h12, h12-abc, h5, x10

How would i achieve an ordering like this:

h1, h5, h10, h12, h12-abc, h1000, x10

The hostnames always begin with a char, then 1-4 digits and partly an extension, like for example '-abc'.

I guess i have to use Substr() to extract the number and order the numbers somehow, that '10' will not be listed before '5'.

With a search i found some old examples with extra() but the Django documentation says it will be deprecated in future and 'Use this method as a last resort' https://docs.djangoproject.com/en/2.1/ref/models/querysets/#extra

What is a future-proof way to do it?

Answer 1

you can use the f-expressions

from django.db.models import F, Value, TextField, IntegerField
from django.contrib.postgres.fields import ArrayField
from django.db.models.expressions import Func

sql = ordModel.objects.annotate(
        num=Cast(
            Func(
                F('hostname'),
                Value("\d+"),
                function='regexp_matches',
            ),
            output_field=ArrayField(IntegerField())
        ),
        char=Func(
            F('hostname'),
            Value("\D+"),
            function='regexp_matches',
            output_field=ArrayField(TextField())
        )
    ).order_by('char', 'num', ).values('hostname')

my result for the same list of values is:

<QuerySet [
{'hostname': 'h1'},
{'hostname': 'h5'},
{'hostname': 'h10'},
{'hostname': 'h12'},
{'hostname': 'h12-abc'},
{'hostname': 'h1000'},
{'hostname': 'x10'}]>

about the database function you can readL regexp_match