Question 17430 in Laravel Question posted in
The official Laravel Documentation can be found here.

sql query last_messages displays wrong message – Laravel

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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search