How to get Substr when annotate Django-CodePudding

I'm try to sort by order_number had many kind

Input:

ADC123
ADC14
ADC23
ERD324
ERD12

Sort default just sort by alphabet

Expected results (Sort only by number):

ERD12
ADC14
ADC23
ADC123
ERD324

Code example:

Person.objects.annotate(
    order_only_number=AddField(Substr("order_number", 1))
).order_by("order_only_number")

CodePudding user response：

Like the comment said you should Cast your field to integer, also you need to fix the Substr method because the index start from 1 and your numbers start from index 4.

Your query should be like this:

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

Person.objects.annotate(
    order_only_number= Cast(Substr("order_number", 4), IntegerField())
).order_by("order_only_number")

CodePudding user response：

Please don't do this. If your order number contains an alpha part and a numerical part, you can use two fields, like:

class Person(models.Model):
    order_alpha_part = models.CharField()
    order_number_part = models.IntegerField()
    
    @property
    def order_number(self):
        return f'{self.order_alpha_part}{order_number_part}'

then you thus can use .order_by('order_number_part'). The property allows you to access the .order_number which will concatenate the alpha part with the number part.