Laravel与MySQL中使用GROUP BY时如何获取分组内最新的一条记录

问题原因 #

你当前的写法失效是因为SQL的执行顺序优先处理GROUP BY再处理ORDER BY，你写的排序逻辑在分组之后才生效，所以分组时会默认取每个users.id分组下的第一条（也就是时间最早的）消息记录，和你的预期不符。

解决方案1：子查询先取最新消息ID（兼容MySQL5.7及以上） #

先通过子查询查询出每个联系用户对应的最新消息主键ID，再关联两张表拿到完整数据，代码示例如下：

// 第一步：先查询每个关联用户对应的最新消息ID
$latestMessageSub = Message::selectRaw('MAX(id) as latest_msg_id, 
    IF(sender_id = ?, receiver_id, sender_id) as contact_user_id', [Auth::id()])
    ->where(function ($q) {
        $q->where('sender_id', Auth::id())
          ->orWhere('receiver_id', Auth::id());
    })
    ->groupBy('contact_user_id');

// 第二步：关联消息表、用户表拿到完整数据
$users = DB::table(DB::raw("({$latestMessageSub->toSql()}) as latest_msgs"))
    ->mergeBindings($latestMessageSub->getQuery())
    ->join('messages', 'latest_msgs.latest_msg_id', '=', 'messages.id')
    ->join('users', 'latest_msgs.contact_user_id', '=', 'users.id')
    ->orderBy('messages.created', 'desc')
    ->paginate();

解决方案2：窗口函数ROW_NUMBER()（仅支持MySQL8.0+/MariaDB10.2+） #

如果你的数据库版本支持窗口函数，可以用更简洁的写法，给每个用户的消息按时间倒序排名，取排名第一的记录即可：

$users = Message::selectRaw('messages.*, users.*,
    ROW_NUMBER() OVER (PARTITION BY IF(messages.sender_id = ?, messages.receiver_id, messages.sender_id) ORDER BY messages.created DESC) as rn', [Auth::id()])
    ->join('users', function ($join) {
        $join->on('messages.sender_id', '=', 'users.id')
             ->orOn('messages.receiver_id', '=', 'users.id');
    })
    ->where(function ($q) {
        $q->where('sender_id', Auth::id())
          ->orWhere('receiver_id', Auth::id());
    })
    ->having('rn', '=', 1)
    ->orderBy('messages.created', 'desc')
    ->paginate();

注意事项 #

• 第一种方案兼容性更好，不需要高版本数据库支持，性能也更稳定，更推荐在生产环境使用
• 如果你之前的SQL模式关闭了ONLY_FULL_GROUP_BY，也可以用子查询先把所有消息按时间倒序排再分组，但是这种写法不符合SQL规范，后续数据库版本升级可能会失效，不建议使用

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