Laravel中获取分组最新记录：Eloquent关系与SQL策略解析

$users = Message::join('users',  function ($join) {
            $join->on('messages.sender_id', '=', 'users.id')
                ->orOn('messages.receiver_id', '=', 'users.id');
        })
        ->where(function ($q) {
            $q->where('messages.sender_id', Auth::user()->id)
            ->orWhere('messages.receiver_id', Auth::user()->id);
        })
        ->orderBy('messages.created', 'desc') // 尝试按时间倒序
        ->groupBy('users.id') // 按用户分组
        ->paginate();

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Message extends Model
{
    use HasFactory;

    // 假设你的时间戳字段是 'created' 而非 'created_at'
    // 如果是 'created_at'，则无需设置 $timestamps 和 CREATED_AT
    const CREATED_AT = 'created';
    const UPDATED_AT = null; // 如果没有 updated_at 字段

    protected $fillable = [
        'sender_id',
        'receiver_id',
        'content',
        'created', // 确保你的时间戳字段在此
    ];

    /**
     * 消息的发送者
     */
    public function sender()
    {
        return $this->belongsTo(User::class, 'sender_id');
    }

    /**
     * 消息的接收者
     */
    public function receiver()
    {
        return $this->belongsTo(User::class, 'receiver_id');
    }
}

use App\Models\Message;
use Illuminate\Support\Facades\Auth;

// 获取当前用户ID
$currentUserId = Auth::id();

// 查询所有与当前用户相关的消息，并预加载发送者和接收者信息
$messages = Message::with(['sender', 'receiver'])
    ->where(function ($query) use ($currentUserId) {
        $query->where('sender_id', $currentUserId)
            ->orWhere('receiver_id', $currentUserId);
    })
    ->orderByDesc('created') // 假设你的时间戳字段是 'created'
    // 如果是 'created_at'，请使用 orderByDesc('created_at')
    ->paginate(15); // 分页显示结果

SELECT m1.*, u_sender.name as sender_name, u_receiver.name as receiver_name
FROM messages m1
JOIN (
    SELECT
        LEAST(sender_id, receiver_id) AS user_pair_id_1,
        GREATEST(sender_id, receiver_id) AS user_pair_id_2,
        MAX(created) AS latest_created_at
    FROM messages
    WHERE sender_id = [current_user_id] OR receiver_id = [current_user_id]
    GROUP BY user_pair_id_1, user_pair_id_2
) AS latest_messages ON (
    (m1.sender_id = latest_messages.user_pair_id_1 AND m1.receiver_id = latest_messages.user_pair_id_2) OR
    (m1.sender_id = latest_messages.user_pair_id_2 AND m1.receiver_id = latest_messages.user_pair_id_1)
) AND m1.created = latest_messages.latest_created_at
LEFT JOIN users u_sender ON m1.sender_id = u_sender.id
LEFT JOIN users u_receiver ON m1.receiver_id = u_receiver.id
WHERE m1.sender_id = [current_user_id] OR m1.receiver_id = [current_user_id]
ORDER BY m1.created DESC;

use Illuminate\Support\Facades\DB;
use App\Models\Message;
use Illuminate\Support\Facades\Auth;

$currentUserId = Auth::id();

$latestMessagePerConversation = Message::select('messages.*')
    ->selectRaw('CASE WHEN messages.sender_id = ? THEN messages.receiver_id ELSE messages.sender_id END as conversation_partner_id', [$currentUserId])
    ->joinSub(function ($query) use ($currentUserId) {
        $query->from('messages')
            ->select(
                DB::raw('LEAST(sender_id, receiver_id) as user_a'),
                DB::raw('GREATEST(sender_id, receiver_id) as user_b'),
                DB::raw('MAX(created) as latest_created')
            )
            ->where('sender_id', $currentUserId)
            ->orWhere('receiver_id', $currentUserId)
            ->groupBy('user_a', 'user_b');
    }, 'latest_conversations', function ($join) {
        $join->on(function ($on) {
            $on->whereColumn('messages.sender_id', '=', 'latest_conversations.user_a')
               ->whereColumn('messages.receiver_id', '=', 'latest_conversations.user_b');
        })->orOn(function ($on) {
            $on->whereColumn('messages.sender_id', '=', 'latest_conversations.user_b')
               ->whereColumn('messages.receiver_id', '=', 'latest_conversations.user_a');
        });
        $join->on('messages.created', '=', 'latest_conversations.latest_created');
    })
    ->with(['sender', 'receiver']) // 预加载发送者和接收者
    ->where(function ($query) use ($currentUserId) {
        $query->where('messages.sender_id', $currentUserId)
              ->orWhere('messages.receiver_id', $currentUserId);
    })
    ->orderByDesc('messages.created')
    ->paginate(15);

use App\Models\Message;
use Illuminate\Support\Facades\Auth;

$currentUserId = Auth::id();

// 获取所有相关消息，按时间倒序
$allMessages = Message::with(['sender', 'receiver'])
    ->where(function ($query) use ($currentUserId) {
        $query->where('sender_id', $currentUserId)
            ->orWhere('receiver_id', $currentUserId);
    })
    ->orderByDesc('created')
    ->get(); // 注意这里是 get() 而非 paginate()

$latestMessagesPerUser = [];
foreach ($allMessages as $message) {
    // 确定对话伙伴的ID
    $partnerId = ($message->sender_id === $currentUserId) ? $message->receiver_id : $message->sender_id;

    // 如果该伙伴的最新消息尚未记录，或当前消息更新，则记录
    if (!isset($latestMessagesPerUser[$partnerId])) {
        $latestMessagesPerUser[$partnerId] = $message;
    }
    // 由于我们已经按时间倒序，第一个遇到的就是最新的，所以不需要再比较时间
}

// $latestMessagesPerUser 现在包含了每个联系人的最新消息
// 如果需要分页，可以在此手动处理数组，或在前端进行分页
$paginatedLatestMessages = collect($latestMessagesPerUser)->values()->paginate(15); // 需要自定义分页器