如何将指定带自定义排序的SQL查询转换为Django ORM查询？

原SQL的核心逻辑是：从devices_device表中查询name字段，先按name开头的数字串转整数排序，再按数字之后的剩余文本排序。以下是对应的Django ORM实现方案：

实现代码（适配PostgreSQL） #

如果你的项目使用PostgreSQL数据库，推荐用Django内置的正则提取函数实现，代码更简洁：

from django.db.models import IntegerField
from django.db.models.functions import Cast, RegexpExtract

# 假设模型类名为Device，对应devices_device表
result = Device.objects.annotate(
    # 提取name开头的数字串并转为整数
    number_part=Cast(
        RegexpExtract('name', r'^[0-9]+'),
        output_field=IntegerField()
    ),
    # 提取name中第一个非数字字符开始的所有内容
    text_part=RegexpExtract('name', r'[^0-9].*')
).order_by('number_part', 'text_part').values_list('name', flat=True)

兼容无开头数字的场景 #

如果存在name字段开头没有数字的情况，直接转换会报错，可以用Coalesce给空值设置默认值：

from django.db.models import IntegerField, Value
from django.db.models.functions import Cast, RegexpExtract, Coalesce

result = Device.objects.annotate(
    number_part=Cast(
        # 若提取不到数字，用'0'作为默认值
        Coalesce(RegexpExtract('name', r'^[0-9]+'), Value('0')),
        output_field=IntegerField()
    ),
    text_part=RegexpExtract('name', r'[^0-9].*')
).order_by('number_part', 'text_part').values_list('name', flat=True)

通用数据库适配方案（自定义Func） #

如果使用其他数据库（如MySQL），可以直接用Func封装原生SQL函数，确保和原SQL逻辑完全一致：

from django.db.models import Func, IntegerField

result = Device.objects.annotate(
    number_part=Func(
        Func('name', function='SUBSTRING', template="%(function)s(%(expressions)s FROM '^[0-9]+')"),
        function='CAST',
        template="%(function)s(%(expressions)s AS INTEGER)",
        output_field=IntegerField()
    ),
    text_part=Func(
        'name',
        function='SUBSTRING',
        template="%(function)s(%(expressions)s FROM '[^0-9].*')"
    )
).order_by('number_part', 'text_part').values_list('name', flat=True)

代码说明 #

• annotate：添加两个临时字段用于排序，对应原SQL中ORDER BY的两个表达式
• order_by('number_part', 'text_part')：和原SQL的排序逻辑完全一致，先按数字整数排序，再按剩余文本排序
• values_list('name', flat=True)：只返回name字段的列表，对应原SQL的select name

内容的提问来源于stack exchange，提问作者Vahid