如何使用SqlAlchemy对斜杠分隔的层级字符串列实现正确自然排序

原直接按字段排序得到的是字典序，因为字符串比较是逐字符按ASCII码对比，10的首字符ASCII码小于2，所以会出现0/0/10排在0/0/2前面的异常结果，核心解决思路是将路径按分隔符拆分后转成整数再排序。

1. 数据库端排序（推荐，适合数据量大的场景） #

不同数据库的字符串拆分、类型转换函数有差异，对应实现如下：

PostgreSQL 实现 #

PostgreSQL原生支持数组类型，可以直接将拆分后的字符串转成整数数组排序：

from sqlalchemy import func

# column替换为你实际的字段名
query = db.session.query(Model.Table).order_by(
    func.cast(func.string_to_array(Model.Table.column, '/'), db.ARRAY(db.Integer))
)

MySQL 实现 #

MySQL没有原生整数数组排序能力，如果你已知字段的最大分层数（比如最多5层），可以逐层拆分转整数排序：

from sqlalchemy import func

query = db.session.query(Model.Table).order_by(
    # 按第1层数值排序
    func.cast(func.substring_index(Model.Table.column, '/', 1), db.Integer),
    # 按第2层数值排序
    func.cast(func.substring_index(func.substring_index(Model.Table.column, '/', 2), '/', -1), db.Integer),
    # 按第3层数值排序
    func.cast(func.substring_index(func.substring_index(Model.Table.column, '/', 3), '/', -1), db.Integer),
    # 按需补充更多层级的排序规则
)

SQLite 实现 #

SQLite 3.33.0及以上版本支持split_part函数，和MySQL逻辑类似逐层排序即可：

from sqlalchemy import func

query = db.session.query(Model.Table).order_by(
    func.cast(func.split_part(Model.Table.column, '/', 1), db.Integer),
    func.cast(func.split_part(Model.Table.column, '/', 2), db.Integer),
    func.cast(func.split_part(Model.Table.column, '/', 3), db.Integer),
    # 按需补充更多层级的排序规则
)

2. Python端排序（适合数据量小的场景） #

如果查询返回的结果量级很小，可以先全量查询后在内存中排序：

# 全量查询数据
items = db.session.query(Model.Table).all()
# 按拆分后的整数元组排序，column替换为实际字段名
sorted_items = sorted(items, key=lambda x: tuple(map(int, x.column.split('/'))))

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