Laravel聊天系统会话列表获取伙伴名称的查询优化问题

嘿，这个问题我熟！你现在的核心问题是JOIN用户表时固定关联了user_one，导致当你是会话发起方（user_one）时，拿到的是自己的名字，而非聊天伙伴的。咱们来调整查询逻辑，确保不管你是user_one还是user_two，都能精准获取伙伴信息。

问题根源分析 #

原查询里的join('users', 'users.id', '=', 'conversation.user_one')是硬关联到user_one，这就造成：

• 当你是user_two时，user_one是聊天伙伴，能正确拿到名字
• 当你是user_one时，user_one是你自己，自然拿不到伙伴的名字

另外，原WHERE条件的逻辑存在优先级问题，orWhere会和前置条件混在一起，可能查询出不属于你的会话。

优化后的查询代码 #

我们用动态关联用户表的方式，通过CASE语句判断你在会话中的角色，从而关联到对应的伙伴ID；同时用子查询精准获取每个会话的最后一条消息：

$currentUserId = Auth::user()->id;

$conversations = DB::table('conversation')
    ->select(
        'latest_msg.message as last_message',
        'latest_msg.created_at as last_message_time',
        'conversation.id as conversation_id',
        'users.name as partner_name',
        'users.firstname as partner_firstname'
    )
    // 子查询：获取每个会话的最新一条消息
    ->joinSub(
        DB::table('messages')
            ->select('conversation_id', 'message', 'created_at')
            ->orderBy('created_at', 'desc')
            ->groupBy('conversation_id'),
        'latest_msg',
        'latest_msg.conversation_id',
        '=',
        'conversation.id'
    )
    // 动态关联伙伴的用户信息：判断当前用户是user_one还是user_two，关联对应的另一方
    ->join('users', function ($join) use ($currentUserId) {
        $join->on(
            'users.id',
            '=',
            DB::raw("CASE WHEN conversation.user_one = {$currentUserId} THEN conversation.user_two ELSE conversation.user_one END")
        );
    })
    // 过滤出当前用户参与的所有会话
    ->where(function ($query) use ($currentUserId) {
        $query->where('conversation.user_one', $currentUserId)
              ->orWhere('conversation.user_two', $currentUserId);
    })
    // 按最后一条消息时间倒序排列
    ->orderBy('latest_msg.created_at', 'desc')
    ->get();

代码逻辑说明 #

1. 子查询获取最新消息：用joinSub创建子查询，确保每个会话只取最后一条消息，避免GROUP BY在数据库严格模式下的字段兼容问题。
2. 动态关联伙伴用户：通过CASE语句动态判断伙伴ID：如果当前用户是user_one，则伙伴是user_two，反之则是user_one，确保关联到正确的用户信息。
3. 修正WHERE条件：用闭包包裹user_one和user_two的判断，保证查询的是当前用户参与的会话，逻辑更严谨。

调整后，不管你是会话发起方还是接收方，会话列表里都能正确显示聊天伙伴的名称啦！

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