Laravel ORM下按group_id获取最新已审批记录的MySQL实现方案

问题分析 #

原查询的核心问题是逻辑顺序错误：先筛选每组最后一条记录，再过滤status=APPROVED，导致像group_id=90这类分组（最新记录为DRAFT）直接被排除，无法获取该组最新的已审批记录（id=4）。需要调整逻辑，先为每个分组锁定符合要求的目标记录，再做关联和权限过滤。

实现思路 #

利用MySQL窗口函数ROW_NUMBER()，针对每个group_id按以下规则排序：

• 优先将status=APPROVED的记录排在前面（权重更高）
• 同状态内按id降序排列（确保取最新的记录）
  最后筛选每个分组中行号为1的记录，再关联模型并过滤用户权限。

Laravel ORM 实现代码 #

$userId = get_current_user_id(); // 替换为实际获取当前用户ID的逻辑

$query = DB::table(DB::raw('(
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY group_id 
            ORDER BY 
                CASE WHEN status = "APPROVED" THEN 1 ELSE 0 END DESC,
                id DESC
        ) as row_num
    FROM approvals
) as filtered_approvals'))
->where('row_num', 1)
->where('status', 'APPROVED') // 确保最终返回的都是已审批记录
->with(['app'])
->whereRelation('app', 'created_by', '=', $userId)
->orderBy('filtered_approvals.id', 'DESC');

return $query;

逻辑解释 #

1. 窗口函数分组排序：通过PARTITION BY group_id拆分数据组，ORDER BY子句先把已审批记录前置，再按ID倒序，这样每个分组里最新的已审批记录会被标记为row_num=1；即使分组最新是DRAFT，该组内最新的已审批记录依然会被标记为row_num=1。
2. 筛选目标记录：只保留row_num=1且status=APPROVED的记录，完全匹配需求。
3. 关联与权限过滤：通过with(['app'])关联对应模型，whereRelation过滤当前用户创建的应用审批记录。

验证结果 #

针对示例数据：

• group_id=500：返回id=1的记录（该组唯一已审批记录）
• group_id=501：返回id=5的记录（该组最新已审批记录）
• group_id=90：返回id=4的记录（跳过id=6的DRAFT，取该组最新已审批记录）

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