SQLAlchemy的order_by无法对URL排序？问题排查与解决

问题背景 #

开发基于Flask+SQLAlchemy的应用时，需要对竞品URL做一致排序：不管URL以http://还是https://开头，同竞品的URL必须排在一起。使用SQLite作为测试库，Competitor模型的url字段为db.String(256)，尝试多种排序写法均未生效，后续排查发现是**将查询结果直接赋值给关系属性competitors**导致的，改为赋值给独立变量后排序恢复正常。

正确的SQL端排序实现 #

要实现忽略协议前缀的不区分大小写排序，需要先统一移除http://和https://前缀，再转小写排序。之前的两次独立replace写法存在逻辑漏洞（比如先移除http://后，https://会变成s://无法被后续处理），以下是两种可靠的实现方式：

方法1：嵌套Replace处理双前缀 #

通过嵌套replace先移除https://，再移除http://，确保所有协议前缀都被清理：

from sqlalchemy import func

# 赋值给独立变量，避免关系属性覆盖排序
product_competitors = Competitor.query.filter_by(product_id=product.id)\
    .order_by(
        func.lower(
            func.replace(
                func.replace(Competitor.url, 'https://', ''),
                'http://', ''
            )
        ).asc()
    ).all()

方法2：SQLite专属Trim优化 #

利用SQLite的ltrim函数一次性移除前缀中的所有相关字符，写法更简洁：

from sqlalchemy import func

product_competitors = Competitor.query.filter_by(product_id=product.id)\
    .order_by(
        func.lower(
            func.ltrim(Competitor.url, 'htps:/')
        ).asc()
    ).all()

避免排序失效的注意事项 #

如果需要通过模型关系（比如product.competitors）直接获取排序后的结果，不要手动查询后赋值给关系属性，而是在定义关系时直接指定默认排序规则：

# 在Product模型中定义关系时配置默认排序
class Product(db.Model):
    # 其他字段...
    competitors = db.relationship(
        'Competitor',
        order_by=func.lower(func.replace(func.replace(Competitor.url, 'https://', ''), 'http://', '')).asc()
    )

这样每次访问product.competitors时，都会自动按指定规则返回排序后的结果。

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