SQLAlchemy按name去重 优先取active为true记录无则取最后一条

问题根因 #

原有代码的JOIN条件使用了OR逻辑，会同时匹配两类记录：

• 对应name分组下id最大的记录
• 全表所有active = true的记录
  当某个name分组下同时存在活跃记录、以及id更大的非活跃记录时，两类记录都会被返回，导致同一个name出现重复结果，不符合需求。

解决方案 #

使用SQL窗口函数ROW_NUMBER()实现分组内按规则排序，取每个分组排序第一的记录即可，修改点如下：

1. 顶部导入新增over函数：

from sqlalchemy import create_engine, Column, Integer, String, Boolean, and_, or_, over

2. 替换CrudData类中的get_auto_all_active方法：

def get_auto_all_active(self, db):
    # 定义窗口排序规则：按name分组，先按active倒序（true优先级更高），再按id倒序（大id优先级更高）
    row_num = over(
        func.row_number(),
        partition_by=self.model.name,
        order_by=[self.model.active.desc(), self.model.id.desc()]
    ).label("row_num")
    # 给所有记录增加分组内排序序号
    subquery = db.query(self.model, row_num).subquery()
    # 仅取每个分组排序第一的记录
    result = db.query(self.model).select_from(subquery).filter(subquery.c.row_num == 1)
    return result.all()

验证结果 #

修改后运行代码，输出和预期完全一致：

1 | test | 4321 | True
4 | toast | trewq | False

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