[FIXED] Sort objects by a substring of a model field in django

Issue

Im trying to find best solution to sort through all Machine objects in my db and find last deviceSerialNo used.

deviceSerialNo is a character field and has a structure like this: AB12-12344.

My task is to sort all Machine objects by a substring of deviceSerialNo field(everything after '-' in deviceSerialNo. My current solution that kind of works

last = Machine.objects.all().order_by('-deviceSerialNo').first().deviceSerialNo

or

last2 = Machine.objects.all().order_by('-deviceSerialNo').annotate(search_index=StrIndex('deviceSerialNo', V('-'))).first().deviceSerialNo

Can someone help me sort it as I mentioned above?

Solution

You can order by a field created with annotate:

from django.db.models import IntegerField, Value as V
from django.db.models.functions import Cast, StrIndex, Substr

last = (
    Machine.objects.annotate(
        part=Cast(Substr("deviceSerialNo", StrIndex("deviceSerialNo", V("-"))), IntegerField())
    )
    .order_by("part")
    .first()
    .deviceSerialNo
)

Just like you had we start by getting the index of the - character:

StrIndex('deviceSerialNo', V('-'))

We then take use Substr to get the second part including the - character:

Substr("deviceSerialNo", StrIndex("deviceSerialNo", V("-")))

Then we cast it to an IntegerField, sort and get the first object. Note: We can get the first object as the integer cast of "-12344" is a negative number.

Answered By - ikkuh