How to get Substr when annotate Django

Question

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")

I think you are on a correct lead with you Substr expression. It could work if you always have same starting position, you could then use Substr and Cast to have the result in a numeric field. With that being said, I'm wondering about the performance cost of such queries, maybe it could be worth denormalize the numeric part and order on that separate field instead? — jkoestinger, Feb 08 '22 at 10:19
Please don't do this that way: if your order number has an alpha part and numerical part, it is probably better to make two fields. — Willem Van Onsem, Feb 08 '22 at 11:38

score 1 · Answer 1 · answered Feb 08 '22 at 11:00

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")

score 0 · Answer 2 · answered Feb 08 '22 at 11:40

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.

How to get Substr when annotate Django

2 Answers2