如何让SQLAlchemy子查询保留ORM实体类型而非Row对象？

你当前的写法返回list[Row]而非list[Table]，是因为外层查询直接针对包含rank字段和Table所有列的子查询结果集，返回的是包含多个字段的Row对象，而非ORM实体实例。要获取Table类型的结果，有两种可行方案：

方案一：调整外层查询，直接选取子查询中的Table实体 #

当子查询中已经包含完整的Table列时，你可以在外层查询中明确选取子查询里的Table复合元素：

subquery = db.query(
    func.rank()
    .over(
        order_by=Table.CreatedAt.desc(),
        partition_by=[Table.ID],
    )
    .label("rank"),
    Table,
).subquery()

# 外层查询明确选取子查询中的Table实体
rows: list[Table] = db.query(subquery.c[Table]).filter(subquery.c.rank == 1).all()

原理：在子查询中传入Table作为查询项时，SQLAlchemy会将其封装为子查询中的一个复合元素，通过subquery.c[Table]可以直接定位到这个元素，外层查询选取它就能返回完整的Table ORM实例。

方案二：使用CTE关联查询（推荐） #

这种方式将窗口函数的计算结果作为公共表表达式（CTE），再与原表通过主键关联，直接查询Table实体，代码更清晰且性能更优：

from sqlalchemy import func

# 定义CTE，仅计算rank和主键（用于关联）
cte = db.query(
    func.rank()
    .over(
        order_by=Table.CreatedAt.desc(),
        partition_by=[Table.ID],
    )
    .label("rank"),
    Table.id,
).cte()

# 关联CTE筛选出rank=1的记录，返回Table实例
rows: list[Table] = db.query(Table).join(
    cte, Table.id == cte.c.id
).filter(cte.c.rank == 1).all()

原理：CTE仅保留必要的rank和主键字段，外层通过主键关联原表，直接查询ORM实体，自然返回list[Table]类型，同时保留所有ORM方法和属性，避免子查询中冗余的字段数据。

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