如何获取当前用户与其他用户的最新对话消息（排除自身ID）

需要从数据库中获取当前用户（ID为1）与其他用户对话的最新消息列表，数据表包含fromUser、toUser、message、date字段，要求：

• 每个对话对仅保留双方发送的最新一条消息
• userPair字段只显示对方用户ID，不显示自身ID

示例数据 #

原消息表 #

期望结果 #

已尝试的实现及问题 #

示例1 #

$results = DB::table("messages")
        ->selectRaw('GREATEST("fromUser", "toUser") as user1, LEAST("fromUser", "toUser") as user2, MAX(created_at) AS maxdate')
        ->where('fromUser', $user->id)
        ->orWhere('toUser', $user->id)
        ->groupByRaw('GREATEST("fromUser", "toUser"), LEAST("fromUser", "toUser")')
        ->get()
        ->flatten();

• 错误使用created_at字段，数据表实际字段为date
• 仅获取分组后的最大时间，未关联原表拿到对应message内容
• 未处理userPair只显示对方ID的需求

示例2 #

$results = ModelsMessages::where(function($query)use($user){
            $query->where('toUser', $user->id)->orWhere('fromUser', $user->id)->orderBy("created_at");
        })->select("toUser", "fromUser")->distinct()->get();

• distinct()无法确保拿到每个对话的最新消息，仅能去重用户ID组合
• 未查询message字段，不符合需求
• 未生成userPair字段

示例3 #

$results = DB::table('messages')
        ->select('*')
        ->where(function($query) use($user){
            ->where('fromUser', $user->id)
            ->orWhere('toUser', $user->id)
        })
        ->orderBy('created_at', 'desc')
        ->limit(1)
        ->unionAll(function ($query) use ($user) {
            $query->select('*')
                ->from('messages')
                ->orderBy('created_at', 'desc')
                ->limit(1);
        })
        ->get()->flatten();

• 语法错误：闭包内的->where多了前置箭头，无法正常执行
• unionAll仅重复获取单条最新消息，无法按对话分组获取每个对话的最新记录

正确实现方案 #

方法1：子查询关联（兼容低版本MySQL） #

先通过子查询获取每个对话的最新消息时间，再关联原表拿到对应消息，同时生成userPair字段：

$userId = 1;

$latestMessages = DB::table('messages')
    ->select([
        DB::raw('CASE WHEN fromUser = ? THEN toUser ELSE fromUser END as userPair'),
        'message'
    ])
    ->whereIn('date', function($query) use ($userId) {
        $query->select(DB::raw('MAX(date)'))
            ->from('messages')
            ->where(function($q) use ($userId) {
                $q->where('fromUser', $userId)
                  ->orWhere('toUser', $userId);
            })
            ->groupBy(DB::raw('CASE WHEN fromUser = ? THEN toUser ELSE fromUser END', [$userId]));
    })
    ->where(function($query) use ($userId) {
        $query->where('fromUser', $userId)
              ->orWhere('toUser', $userId);
    })
    ->get();

方法2：窗口函数（MySQL 8.0+ 推荐） #

利用ROW_NUMBER()窗口函数按对话分组并排序，直接取每组第一条（最新）消息：

$userId = 1;

$latestMessages = DB::table(DB::raw('
    (SELECT 
        *, 
        CASE WHEN fromUser = ? THEN toUser ELSE fromUser END as userPair,
        ROW_NUMBER() OVER (PARTITION BY CASE WHEN fromUser = ? THEN toUser ELSE fromUser END ORDER BY date DESC) as rn 
     FROM messages 
     WHERE fromUser = ? OR toUser = ?) as sub
', [$userId, $userId, $userId, $userId]))
    ->where('rn', 1)
    ->select('userPair', 'message')
    ->get();

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