[Django]-How to order django charfield by alphabetical+numeric

by

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:])))
👤Arry Lee

0👍

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.

👤Belayer

Leave a comment