The `length` and `name` sort params do not meet my requirement

Question

I have a PhysicalServer model:

class PhysicalServer(models.Model):
    name = models.CharField(max_length=32)
    cabinet = models.ForeignKey(to=Cabinet, on_delete=models.DO_NOTHING, related_name="physical_servers")
    physical_server_model = models.ForeignKey(to=PhysicalServerModel, null=True, on_delete=models.DO_NOTHING)
    ...

    class Meta:
        ordering = ['-cabinet', '-physical_server_model', 'name']

its list API view is this:

class PhysicalServerListAPIView(ListAPIView):
    serializer_class = PhysicalServerListSerializer
    permission_classes = [AllowAny]
    pagination_class = CommonPagination
    def get_queryset(self):
        qs = PhysicalServer.objects.filter(**filters)
        return qs.annotate(length=Length('name')).order_by('length', 'name') # there if I put the `name` first(order_by('name', 'length')), also inconformity my requirement.

my physicalserver instance name like this below:

My question is, when I use this for list sort:

return qs.annotate(length=Length('name')).order_by('length', 'name')

the result will be:

SE01-A1
SE01-A2
SE01-A3
...
SE01-A9
SE01-C1
SE01-C2
SE01-C3
...
SE01-A10
SE01-A11
SE01-A12
...

if I use the below for sort:

return qs.annotate(length=Length('name')).order_by('name', 'length')

the result will be:

SE01-A1
SE01-A11
SE01-A12
SE01-A13
...
SE01-A2
SE01-A21
...
SE01-A3
...

How can I sort like this:

SE01-A1
SE01-A2
SE01-A3
SE01-A4
...
SE01-A10
...
SE01-C1
SE01-C2
...

?

You are looking for what is called "natural sorting". Please see similar questions: https://stackoverflow.com/questions/42535451/natural-sort-on-django-queryset, https://stackoverflow.com/questions/27440890/django-query-natural-sort, https://stackoverflow.com/questions/5967500/how-to-correctly-sort-a-string-with-a-number-inside — igrinis, Jun 20 '18 at 13:08

jatinderjit · Answer 1 · 2018-06-25T13:49:37.870

You'll need to extract the numbers from the strings. Postgres and mysql provide regexp_replace function (I'm not sure about other databases). But Django doesn't provide an implementation, so we'll write our own function:

from django.db.models import Func, Value

class RegexpReplace(Func):
    function = 'REGEXP_REPLACE'

    def __init__(self, expression, search, replace, **extra):
        search = Value(search)
        replace = Value(replace)
        super(RegexpReplace, self).__init__(expression, search, replace, **extra)

I'll assume you want to split the names until the digits at the end, and then you want to sort using this first half, and then the numbers at the end. (This will work fine for you until you start getting 3-digit numbers before the hyphen, i.e. SE99-A1, SE100-A1).

from django.db.models import F, IntegerField
from django.db.models.functions import Cast

qs = ... # get your queryset
qs.annotate(
    letters=RegexpReplace(F('name'), '(.*[a-zA-Z])[0-9]+$', r'\1'),
    seq=Cast(
        RegexpReplace(F('name'), '.*[a-zA-Z]([0-9]+)$', r'\1'),
        IntegerField(),
    ),
).order_by('letters', 'seq')

Shouldn't it be `RegexpReplace` instead of `RegexpReplace2` in your second code snippet? — Daniel Hepper, Jun 24 '18 at 13:17

score 0 · Answer 2 · edited Mar 24 '21 at 15:20

0

Use Substr to separate out the first part of the name from the numbers at the end, and then sort by this new annotation first.

from django.db.models.functions import Substr, Length

qs = qs.annotate(letters=Substr('name', 1, 6), length=Length('name'))
qs = qs.order_by('letters', 'length', 'name')
return qs

edited Mar 24 '21 at 15:20

iacob

20,084
6
92
119

answered Jun 19 '18 at 03:16

user2100826

335
2
3
13

1

this maybe works for my case, but in my case the `name` are almost fixed, so, whether there is a better way for doing this? Such as if the length is not fixed, can not use `1, 6` for substr. – qg_java_17137 Jun 19 '18 at 03:55
Well, you can repeat this strategy multiple times as needed, iterating through your possibilities for `name`, but at some point the field must be fixed into a particular format in order for _any_ type of ordering to work. The alternative is to split the field into multiple fields containing the base components that make up the name (this is my suggestion), or else look into writing some database functions -- of course, the more complicated your format is, the longer it will take to run the query. – user2100826 Jun 19 '18 at 11:38

The `length` and `name` sort params do not meet my requirement

2 Answers2