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

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.

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.

That's because it's not legal syntax to place the GROUP BY clause after an ORDER BY - note the formal definition in the docs. http://dev.mysql.com/doc/refman/5.7/en/select.html

It's not clear what you're actually trying to accomplish by this - SQL doesn't have particularly good support for ordered data inside an aggregate.

Thanks all for helping me. At last I solved it, I got help from this answer from another post https://stackoverflow.com/a/7745635/6279602 it works with joins not with group by because group by does not support ordering.