Order by name with number Django

Question

I have approximately 500 device objects in an sqlite db, with a name field such as:

Device-0
Device-1
Device-2
Device-3
...
...
Device-500

When listing these with django, I want it to list based on the number after the semicolon in the name, as shown above.

I tried:

queryset = Device.objects.all().order_by('name')

Also from this question:

queryset = Device.objects.annotate(int_sort=Cast("name", IntegerField())).order_by("int_sort", "name")

Both of these produce this result:

Device-0
Device-1
Device-10
Device-100
Device-101
...

Any help would be greatly appreciated.

Does this solve your problem? https://stackoverflow.com/questions/34559094/django-order-by-custom-function — token, Jun 25 '20 at 11:17
Just rename all the device objects to Device-XYZ. i.e Device-001,Device-002.then do the same thing you did above first one — Pavan kumar, Jun 25 '20 at 11:23

AKX · Accepted Answer · 2020-06-25T15:20:03.330

You're looking for a "natural sort" ("dictionary sort") order.

That's not built-in to SQLite (nor any other database I know of).

If all of your rows do follow a XYZ-123 format, you could

add an .extra() where= column with an expression that splits the column by a dash, then casts the second part to a number
and then order_by that extra column.

Example

Here's an example you can run in your SQLite shell:

sqlite> create table device (name text);
sqlite> insert into device (name) values ('Device-1'),('Device-2'),('Device-3'),('Device-4'),('Device-5'),('Device-6'),('Device-7'),('Device-8'),('Device-9'),('Device-10'),('Device-11'),('Device-12'),('Device-13'),('Device-14'),('Device-15'),('Device-16'),('Device-17'),('Device-18'),('Device-19'),('Device-20'),('Device-21'),('Device-22'),('Device-23'),('Device-24'),('Device-25'),('Device-26'),('Device-27'),('Device-28'),('Device-29'),('Device-30'),('Device-31'),('Device-32'),('Device-33'),('Device-34'),('Device-35'),('Device-36'),('Device-37'),('Device-38'),('Device-39');
sqlite> select * from device order by name limit 10;
Device-1
Device-10
Device-11
Device-12
Device-13
Device-14
Device-15
Device-16
Device-17
Device-18
sqlite> select *, cast(substr(name,instr(name, '-')+1) as number) number from device order by number limit 10;
Device-1|1
Device-2|2
Device-3|3
Device-4|4
Device-5|5
Device-6|6
Device-7|7
Device-8|8
Device-9|9
Device-10|10

With this example, you should (but I didn't verify since I don't have a suitable Django app on my hands) be able to do

Device.objects.all().extra(
  select={'device_number': "cast(substr(name,instr(name, '-')+1) as number)"},
  order_by='device_number',
)

Thanks for the answer. I've had trouble putting your advice into action though. Could you give an example? — Ahmet, Jun 25 '20 at 15:06
This is great, thanks a lot! You have very vast knowledge. In the end what worked is with very little modification: `Device.objects.extra(select={'device_number': "cast(substr(name,instr(name, '-')+1) as number)"}).order_by('device_number')` — Ahmet, Jun 25 '20 at 15:52

Order by name with number Django

1 Answers1

Example