Laravel消息系统SQL查询求助：获取用户聊天及对话方信息

看起来你的原生SQL逻辑有点绕，导致没拿到预期结果。我们先理清楚需求：要获取用户（ID=1）参与的所有聊天，拿到每个聊天的对话方信息，并且按每个聊天的最新消息时间倒序排列。

先给你修正后的原生SQL，你可以先测试这个：

SELECT 
    u.user_id,
    u.name,
    u.surname,
    c.chat_id,
    MAX(m.date) AS last_message_date
FROM chat c
-- 关联用户表，匹配当前用户的对话方：如果当前用户是user1，取user2；反之取user1
JOIN user u ON (c.user1_id = 1 AND u.user_id = c.user2_id) 
             OR (c.user2_id = 1 AND u.user_id = c.user1_id)
-- 关联消息表，用来获取每个聊天的最新消息时间
LEFT JOIN message m ON m.chat_id = c.chat_id
-- 筛选当前用户参与的聊天
WHERE (c.user1_id = 1 OR c.user2_id = 1)
  -- 排除当前用户自己
  AND u.user_id != 1
-- 按聊天分组，确保每个对话方只出现一次
GROUP BY c.chat_id, u.user_id, u.name, u.surname
-- 按最新消息时间倒序排序
ORDER BY last_message_date DESC;

为什么你的原SQL有问题？ #

• 你重复JOIN了两次chat表（a和b），这会导致不必要的关联，甚至产生重复数据或者错误的匹配
• 没有关联message表，根本无法获取消息时间来排序
• WHERE子句的筛选逻辑混乱，b.chat_id IN (...)的写法完全没必要，反而让关联逻辑更复杂

如果你用Laravel Eloquent实现（更优雅） #

首先确保你的模型关联正确：

1. 定义模型关联

User.php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class User extends Model
{
    protected $primaryKey = 'user_id';

    // 获取用户参与的所有聊天
    public function chats(): HasMany
    {
        return $this->hasMany(Chat::class, 'user1_id')
                    ->union($this->hasMany(Chat::class, 'user2_id'));
    }

    // 获取聊天的对话方及最新消息
    public function chatPartners()
    {
        return $this->chats()
            ->join('user', function ($join) {
                $currentUserId = $this->user_id;
                $join->where(function ($query) use ($currentUserId) {
                    $query->where('chat.user1_id', $currentUserId)
                          ->whereColumn('user.user_id', 'chat.user2_id');
                })
                ->orWhere(function ($query) use ($currentUserId) {
                    $query->where('chat.user2_id', $currentUserId)
                          ->whereColumn('user.user_id', 'chat.user1_id');
                });
            })
            ->select('user.*', 'chat.chat_id')
            // 预加载每个聊天的最新消息
            ->with(['latestMessage' => function ($query) {
                $query->latest('date')->first();
            }])
            // 按最新消息时间倒序排序
            ->orderByDesc(function ($query) {
                $query->select('date')
                      ->from('message')
                      ->whereColumn('message.chat_id', 'chat.chat_id')
                      ->latest()
                      ->limit(1);
            });
    }
}

Chat.php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\HasOne;

class Chat extends Model
{
    protected $primaryKey = 'chat_id';
    public $timestamps = false; // 如果chat表没有created_at/updated_at就加这个

    public function user1(): BelongsTo
    {
        return $this->belongsTo(User::class, 'user1_id');
    }

    public function user2(): BelongsTo
    {
        return $this->belongsTo(User::class, 'user2_id');
    }

    public function messages(): HasMany
    {
        return $this->hasMany(Message::class, 'chat_id');
    }

    // 获取聊天的最新消息
    public function latestMessage(): HasOne
    {
        return $this->hasOne(Message::class, 'chat_id')->latest('date');
    }
}

Message.php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class Message extends Model
{
    protected $primaryKey = 'message_id';

    public function chat(): BelongsTo
    {
        return $this->belongsTo(Chat::class, 'chat_id');
    }

    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class, 'user_id');
    }
}

2. 在控制器中获取数据

// 获取认证用户的聊天对话方，按最新消息倒序
$user = auth()->user();
// 如果测试用ID=1的用户，就用 User::find(1)
$chatPartners = $user->chatPartners()->get();

// 输出结果的话，可以遍历查看
foreach ($chatPartners as $partner) {
    echo "对话方：{$partner->name} {$partner->surname}，最新消息时间：{$partner->latestMessage?->date}\n";
}

这样你就能得到符合需求的结果了：每个对话方只出现一次，按聊天的最新消息时间从新到旧排序，同时拿到对话方的用户信息。

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