i have query like this, but displays wrong 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();

when i change

                DB::raw('MAX(messages.body) last_message'),

to

                DB::raw('messages.body ORDER BY messages.created_at DESC LIMIT 1 last_message'),

display error messages like this, syntax error at or near "last_message". How to fix this?

CodePudding user response：

You want to alias the column, not the statement. Try to change it to:

messages.body last_message ORDER BY messages.created_at DESC LIMIT 1 

CodePudding user response：

So you need first order the records based on some column and then return the first record of that list.

$cart_data = ScCart::orderBy('created_at', 'asc')->first();
$cart_data = ScCart::orderBy('created_at', 'desc')->first();

This will work for first and last record created in that table.