SQLAlchemy中nulls_last()展开已标记列问题及替代解法咨询

在SQLAlchemy里定义带label的聚合表达式后，直接用在order_by里能正常识别别名，但调用nulls_last()方法时，会展开原聚合表达式而非使用已定义的别名列名。

示例代码：

priceSum = func.sum(Book.price).label("price_sum")

# 正常使用标记列'price_sum'
print(select(Book.author, priceSum).group_by(Book.author).order_by(priceSum))

# 展开了priceSum表达式而非使用标记
print(select(Book.author, priceSum).group_by(Book.author).order_by(priceSum.nulls_last()))

生成的SQL对比：

-- 未使用nulls_last()
SELECT books.author, sum(books.price) AS price_sum 
FROM books GROUP BY books.author ORDER BY price_sum

-- 使用nulls_last()
SELECT books.author, sum(books.price) AS price_sum 
FROM books GROUP BY books.author ORDER BY sum(books.price) NULLS LAST

目前临时解法是order_by(nulls_last(text('price_sum')))，以下是其他可行解法：

解法1：用column()直接引用别名 #

通过column()函数直接指定别名，再调用nulls_last()，SQL生成时会直接使用别名：

from sqlalchemy import column

priceSum = func.sum(Book.price).label("price_sum")
stmt = select(Book.author, priceSum).group_by(Book.author).order_by(column("price_sum").nulls_last())
print(stmt)

生成的SQL：

SELECT books.author, sum(books.price) AS price_sum 
FROM books GROUP BY books.author ORDER BY price_sum NULLS LAST

解法2：用alias()包装聚合表达式 #

给带label的聚合表达式加上alias()，让它被识别为独立列对象，调用nulls_last()时就会使用别名：

priceSum = func.sum(Book.price).label("price_sum").alias()
stmt = select(Book.author, priceSum).group_by(Book.author).order_by(priceSum.nulls_last())
print(stmt)

解法3：复用查询中已定义的列 #

先构建基础查询，再从查询的columns属性中获取已注册的price_sum列，调用nulls_last()：

priceSum = func.sum(Book.price).label("price_sum")
stmt = select(Book.author, priceSum).group_by(Book.author)
# 从查询列集合中获取目标列
order_col = stmt.columns.price_sum
stmt = stmt.order_by(order_col.nulls_last())
print(stmt)

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