Convert sql query to django orm query

Question

I want to convert a query to Django ORM. How can I do this?

select name from devices_device ORDER BY 
  CAST(SUBSTRING(name FROM '^[0-9]+') AS INTEGER),
  SUBSTRING(name FROM '[^0-9].*')

Answer 1

from django.db.models.functions import Cast, Substr
from django.db.models import IntegerField, F
from devices.models import Device  # Import your Device model

# Query using Django ORM
devices = Device.objects.annotate(
    numeric_part=Cast(Substr('name', 1, (F('name').index_regex('[^0-9]') - 1)), IntegerField()),
    string_part=Substr('name', (F('name').index_regex('[^0-9]'))),
).order_by('numeric_part', 'string_part').values_list('name', flat=True)

# Execute the query
results = list(devices)