Laravel查询last_messages显示错误，修改后报语法错误如何解决？

问题说明 #

使用Laravel Eloquent查询聊天对话列表时，通过MAX(messages.body)获取的last_message内容不正确（MAX()是按字符串字典序取最大值，并非按时间取最新消息）。尝试修改为messages.body ORDER BY messages.created_at DESC LIMIT 1 last_message时，触发PostgreSQL语法错误：syntax error at or near "last_message"。

原查询代码：

$users = Message::join('users',  function ($join) {
    $join->on('messages.from_id', '=', 'users.id')
        ->orOn('messages.to_id', '=', 'users.id');
    })
    ->where(function ($q) {
        $q->where('messages.from_id', auth()->user()->id)
            ->orWhere('messages.to_id', auth()->user()->id);
    })
    ->where('users.id','!=',auth()->user()->id)
    ->select([
        'users.id',
        'users.name',
        'users.avatar',
        DB::raw('MAX(messages.created_at) max_created_at'),
        DB::raw('MAX(messages.body) last_message'),
        DB::raw('CASE WHEN(COUNT(messages.is_read) FILTER (WHERE is_read = false
            AND messages.from_id != '.auth()->user()->id.') = 0) THEN true ELSE false END is_read'),
        DB::raw('COUNT(messages.is_read) FILTER (WHERE is_read = false
            AND messages.from_id != '.auth()->user()->id.') count_unread')
    ])
    ->orderBy('max_created_at', 'desc')
    ->groupBy('users.id')
    ->paginate($request->per_page ?? 20)
    ->withQueryString();

错误原因 #

直接在SELECT中写messages.body ORDER BY ... LIMIT 1不符合PostgreSQL语法规范，GROUP BY分组后必须使用聚合函数，或通过DISTINCT ON、窗口函数等方式获取分组内的特定行数据。

解决方案 #

方法1：使用PostgreSQL专属的DISTINCT ON #

该方式简洁高效，直接按用户分组并取每组最新的消息记录：

$users = Message::select([
        'users.id',
        'users.name',
        'users.avatar',
        'messages.created_at as max_created_at',
        'messages.body as last_message',
        DB::raw('CASE WHEN(COUNT(messages.is_read) FILTER (WHERE is_read = false
            AND messages.from_id != '.auth()->user()->id.') = 0) THEN true ELSE false END is_read'),
        DB::raw('COUNT(messages.is_read) FILTER (WHERE is_read = false
            AND messages.from_id != '.auth()->user()->id.') count_unread')
    ])
    ->join('users', function ($join) {
        $join->on('messages.from_id', '=', 'users.id')
            ->orOn('messages.to_id', '=', 'users.id');
    })
    ->where(function ($q) {
        $q->where('messages.from_id', auth()->user()->id)
            ->orWhere('messages.to_id', auth()->user()->id);
    })
    ->where('users.id', '!=', auth()->user()->id)
    ->distinctOn('users.id')
    ->orderBy('users.id')
    ->orderBy('messages.created_at', 'desc')
    ->orderBy('max_created_at', 'desc')
    ->paginate($request->per_page ?? 20)
    ->withQueryString();

注意：distinctOn是Laravel对PostgreSQL的适配特性，必须先按分组字段（users.id）排序，再按时间倒序，确保每组第一条为最新消息。

方法2：使用窗口函数ROW_NUMBER() #

适合复杂分组场景，通过窗口函数标记每组内的消息排序，筛选出最新的一条：

// 子查询：给每个对话的消息按时间排序，标记行号
$subquery = Message::select([
        '*',
        DB::raw('ROW_NUMBER() OVER (PARTITION BY CASE WHEN from_id = '.auth()->user()->id.' THEN to_id ELSE from_id END ORDER BY created_at DESC) as rn')
    ])
    ->where(function ($q) {
        $q->where('from_id', auth()->user()->id)
            ->orWhere('to_id', auth()->user()->id);
    });

// 关联用户表，筛选出行号为1的最新消息
$users = DB::table('users')
    ->joinSub($subquery, 'latest_messages', function ($join) {
        $join->on('users.id', '=', DB::raw('CASE WHEN latest_messages.from_id = '.auth()->user()->id.' THEN latest_messages.to_id ELSE latest_messages.from_id END'));
    })
    ->where('users.id', '!=', auth()->user()->id)
    ->select([
        'users.id',
        'users.name',
        'users.avatar',
        'latest_messages.created_at as max_created_at',
        'latest_messages.body as last_message',
        DB::raw('CASE WHEN(COUNT(latest_messages.is_read) FILTER (WHERE is_read = false
            AND latest_messages.from_id != '.auth()->user()->id.') = 0) THEN true ELSE false END is_read'),
        DB::raw('COUNT(latest_messages.is_read) FILTER (WHERE is_read = false
            AND latest_messages.from_id != '.auth()->user()->id.') count_unread')
    ])
    ->where('latest_messages.rn', 1)
    ->groupBy('users.id', 'latest_messages.created_at', 'latest_messages.body')
    ->orderBy('max_created_at', 'desc')
    ->paginate($request->per_page ?? 20)
    ->withQueryString();

方法3：子查询获取最新消息ID（兼容更多数据库） #

如果需要兼容非PostgreSQL数据库，可先查询每个用户的最新消息ID，再关联获取内容：

// 子查询：获取每个对话的最新消息ID
$latestMsgIds = Message::select(
        DB::raw('MAX(id) as msg_id'),
        DB::raw('CASE WHEN from_id = '.auth()->user()->id.' THEN to_id ELSE from_id END as user_id')
    )
    ->where(function ($q) {
        $q->where('from_id', auth()->user()->id)
            ->orWhere('to_id', auth()->user()->id);
    })
    ->groupBy('user_id');

// 关联用户表和最新消息表
$users = Message::join('users', function ($join) {
        $join->on('messages.from_id', '=', 'users.id')
            ->orOn('messages.to_id', '=', 'users.id');
    })
    ->joinSub($latestMsgIds, 'latest_ids', function ($join) {
        $join->on('messages.id', '=', 'latest_ids.msg_id');
    })
    ->where(function ($q) {
        $q->where('messages.from_id', auth()->user()->id)
            ->orWhere('messages.to_id', auth()->user()->id);
    })
    ->where('users.id', '!=', auth()->user()->id)
    ->select([
        'users.id',
        'users.name',
        'users.avatar',
        'messages.created_at as max_created_at',
        'messages.body as last_message',
        DB::raw('CASE WHEN(COUNT(messages.is_read) FILTER (WHERE is_read = false
            AND messages.from_id != '.auth()->user()->id.') = 0) THEN true ELSE false END is_read'),
        DB::raw('COUNT(messages.is_read) FILTER (WHERE is_read = false
            AND messages.from_id != '.auth()->user()->id.') count_unread')
    ])
    ->groupBy('users.id', 'messages.created_at', 'messages.body')
    ->orderBy('max_created_at', 'desc')
    ->paginate($request->per_page ?? 20)
    ->withQueryString();

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