Laravel 5.8 获取互发消息用户及最后消息排序问题求助

Hey there! I’ve worked through similar chat conversation queries in Laravel 5.8 before, so let’s break down how to solve this problem for you. The goal is to fetch all users who’ve exchanged messages with the currently logged-in user, paired with the most recent message between each pair, sorted by the timestamp of that last message.

Assumptions About Your Database Structure #

Based on typical chat message setups, I’ll assume your messages table has this core structure (adjust if your migration differs):

// Example messages table migration
Schema::create('messages', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('sender_id');
    $table->unsignedInteger('receiver_id');
    $table->text('content');
    $table->timestamps();

    $table->foreign('sender_id')->references('id')->on('users')->onDelete('cascade');
    $table->foreign('receiver_id')->references('id')->on('users')->onDelete('cascade');
});

Also, make sure your Message model has the necessary relationships defined:

namespace App;

use Illuminate\Database\Eloquent\Model;

class Message extends Model
{
    protected $fillable = ['sender_id', 'receiver_id', 'content'];

    // Relationship to the message sender
    public function sender()
    {
        return $this->belongsTo(User::class, 'sender_id');
    }

    // Relationship to the message receiver
    public function receiver()
    {
        return $this->belongsTo(User::class, 'receiver_id');
    }
}

Core Solution: Fetch Conversations with Last Message #

Here’s a reliable approach that avoids Laravel’s strict groupBy limitations and ensures you get only the latest message per conversation:

use Illuminate\Support\Facades\DB;

// Get the currently logged-in user's ID
$currentUserId = auth()->id();

// Step 1: Get the ID of the most recent message for each unique conversation
$lastMessageIds = Message::where(function ($query) use ($currentUserId) {
        $query->where('sender_id', $currentUserId)
              ->orWhere('receiver_id', $currentUserId);
    })
    ->selectRaw('MAX(id) as last_message_id')
    // Group conversations by the pair of users (regardless of who sent first)
    ->groupBy(DB::raw('LEAST(sender_id, receiver_id), GREATEST(sender_id, receiver_id)'))
    ->pluck('last_message_id');

// Step 2: Fetch those latest messages, load related user data, and sort
$conversations = Message::with(['sender', 'receiver'])
    ->whereIn('id', $lastMessageIds)
    ->orderBy('created_at', 'desc')
    ->get()
    // Format the results to clearly show the other user and last message
    ->map(function ($message) use ($currentUserId) {
        $otherUser = $message->sender_id === $currentUserId ? $message->receiver : $message->sender;
        return [
            'user' => $otherUser,
            'last_message' => $message,
        ];
    });

How This Works #

• Grouping Conversations: Using LEAST() and GREATEST() ensures that conversations between User A and User B are grouped together, whether A sent the first message or B did.
• Fetching Latest Messages: We grab the highest id per group (since IDs are auto-incrementing, this guarantees the most recent message—more reliable than using MAX(created_at) if multiple messages are sent at the exact same time).
• Formatting Results: The map() method cleans up the output to directly give you the other user in the conversation paired with the last message.

Performance Optimizations #

If your messages table grows large, add these indexes to speed up the query:

// Add these to your messages table migration
$table->index(['sender_id', 'receiver_id']);
$table->index('created_at');

内容的提问来源于stack exchange，提问作者Alkaou Dit Baba Dembele