How to order django charfield by alphabetical+numeric

Question

I have the following a model, I already try to order_by('cell_name') but no same what I want. I want to order base on alphabet+number field. Could you please have your suggestions on this??, any help will be appreciated.

#my model

class BuildingCell(models.Model):
    ....
    cell_name = models.CharField(max_length=100, blank=True, null=True)

#my queryset
q1 = BuildingCell.objects.all().order_by('cell_name')

my result:

cell_name
10a
10b
10c
11a
11b
1a
1b
2a
2b

The result that I want:

1a
1b
2a
2b
10a
10b
11a
11b

See [this](https://stackoverflow.com/questions/2969348/django-ordering-numerical-value-with-order-by/) question/answers — Chiheb Nexus, Aug 10 '19 at 08:07

score 2 · Answer 1 · edited Aug 10 '19 at 14:11

2

You can sort the queryset manually:

q1 = BuildingCell.objects.all()
q1 = sorted(list(q1),key=lambda s:list(map(lambda x:int(x) if x.isdigit() else x,re.split(r'(\d+)',s.name)[1:])))

edited Aug 10 '19 at 14:11

Akaisteph7

5,034
2
20
43

answered Aug 10 '19 at 14:07

Arry Lee

21
3

score 0 · Answer 2 · answered Aug 10 '19 at 17:46

Assuming the data format outline is consistent with some number of digits followed by optional text you can parse data into 2 fields via regular expression and sort the resulting fields directly is the SQL statement.

with test_data (cell_name) as
   ( values ('10a')
          , ('10b')
          , ('10c')
          , ('11a')
          , ('11b')
          , ('1a')
          , ('1b')
          , ('2a')
          , ('2b')
          , ('20')      -- added 
          , ('20b')     -- added
          , ('ABC')     -- Use only for no leading digits passibility     
   )
select cell_name
 from  test_data 
 order by
       regexp_replace (cell_name, '^(\d+)(\D?.*)','\1')::integer
     , regexp_replace (cell_name, '^(\d+)(\D?.*)','\2') 
 ;

If it's possible that an entry does not have leading digits then expand the order by clause:

select cell_name
 from  test_name 
 order by
         case when cell_name ~ '^(\d+)'
              then regexp_replace (cell_name, '^(\d+)(\D?.*)','\1')::integer
              else null::integer
         end
       , case when cell_name ~ '^(\d+)'
              then regexp_replace (cell_name, '^(\d+)(\D?.*)','\2')
              else cell_name
         end ;

Converting to the appropriate Django/Python structure I'll have to leave to you as I don't know Django and little Python. Hope this helps.

How to order django charfield by alphabetical+numeric

2 Answers2