0
class Order(models.Model):
   .....
   payment_no = models.CharField(max_length=32, blank=True)
   .....

my model has field payment_no to store string that have 2 syntax

  1. prefix string + '-' + 8 digit number
  2. only 8 digit number

for example 'stk-00000001', 'fad-00000002', '00000003'.

I wanna know how to order only the 8 digit number. please guide me how to order it

Fah Nateecha
  • 151
  • 3
  • 14
  • ideally I'd have two fields, one for prefix, one for the 8 digits. If you can't alter that now, I'd still recommend adding a field for the 8 digits, particularly if you are going to want to order by it. This allows the db to do all the ordering for you efficiently. – AMG Jul 13 '21 at 16:07
  • thank you so much for your advice but do you have any another way except adding the new field cause i cannot migrate old data – Fah Nateecha Jul 13 '21 at 16:15
  • what database backend are you using? You can try an expression: https://stackoverflow.com/a/42259430/4872140 – AMG Jul 13 '21 at 16:16
  • the database that i use is mariadb – Fah Nateecha Jul 13 '21 at 16:21
  • I'm thinking you might be able to annotate, then sort using combination of `StrIndex` and `Substr` looking for the dash. See https://stackoverflow.com/a/46452793/4872140 – AMG Jul 13 '21 at 16:31
  • i try like this Order.objects.filter(status__in=[30, 21, -5]).annotate(ordering=F('purchase_no').split('-')[len(F('purchase_no').split('-'))-1]).order_by('-ordering') but f not have spilt function – Fah Nateecha Jul 13 '21 at 16:32
  • 1
    If you are certain on length of your numerical part, maybe [Right](https://docs.djangoproject.com/en/dev/ref/models/database-functions/#right) is the way to go. – AMG Jul 13 '21 at 16:39
  • 1
    i try this Order.objects.all().annotate(ordering=Right('payment_no', 12)).order_by('-ordering') and its work !! thank you so much you safe my life – Fah Nateecha Jul 13 '21 at 17:19

0 Answers0