在OrderBy之后的GroupBy用于Laravel中的复杂查询

Question

Here is my code for fetching messaging and grouping them for each chat

$query = DB::table('chats');

    $otherUserId= $request->input('loadId');
    if($otherUserId==0){
        $query->join('users as u1', 'u1.id', '=', 'chats.user1_id');
        $query->join('users as u2', 'u2.id', '=', 'chats.user2_id');
        $query->join('messages', 'messages.chat_id', '=', 'chats.id');
        $query->where('chats.user1_id', '=', '{$userId}');
        $query->orWhere('chats.user2_id', '=', '{$userId}');
        $query->select('messages.chat_id', 'messages.from_id', 'u1.id as    user1_id', 'u2.id as user2_id', 'messages.created_at', 'messages.body', 'messages.read', 'u1.name as user1_name', 'u2.name as user2_name', 'u1.picture_url as user1_pic', 'u2.picture_url as user2_pic');

        $query->orderBy('messages.created_at ', 'DESC');
        $query->groupBy('chat_id');
        $messages = $query->paginate(4);

When I run the query the result is not as expected OrderBy comes after group by. I've tried a lot but could't find and solution. I tried this solution also but couldn't make it work. Order By before Group By using Eloquent (Laravel)

Please help me I've lost a lot of time trying to sort it out. Thanks in advance for helping me.

Answer 1

One line before the paginate add the following code

dd($query->toSql());

This will return the query string in order to debug the mysql query. If you still can't find out what is happening paste it here to help you.