GroupBy after orderBy for complicated query in 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.

score 0 · Answer 1 · answered Aug 22 '16 at 22:43

0

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.

answered Aug 22 '16 at 22:43

H.Kontodios

246
1
7

score 0 · Answer 2 · answered Aug 22 '16 at 22:48

0

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.

answered Aug 22 '16 at 22:48

Sam Dufel

17,560
3
48
51

Thanks @Sam Dufel, I didn't know that **Group by** is not legal after **order By** . But what I need to do is to get 1 latest message of every chat (there are many messages related to one chat) , could you suggest any solution fro that. Thanks again for you help. – Logiclicker Aug 23 '16 at 05:05

score 0 · Answer 3 · edited May 23 '17 at 10:29

0

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.

edited May 23 '17 at 10:29

Community

1
1

answered Aug 25 '16 at 18:31

Logiclicker

21
3

GroupBy after orderBy for complicated query in Laravel

3 Answers3

Linked