Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
What I want to do is grouping comments written by a user with same subjects, and get the latest (most-recent) one. I tried using Comment::where()->orderBy()->groupBy(), but it isn't returning data as expected.
This is my database:
| id | receiver_id | sender_id | title | body | created_at |
| 13 | 2 | 5 | Art | DD | 12.30... |
| 12 | 2 | 5 | Art | CC | 12.20... |
| 11 | 2 | 5 | Art | BB | 12.10... |
| 10 | 2 | 5 | Art | AA | 12.00... |
| 9 | 2 | 3 | Msc | XX | 11.30... |
| 8 | 2 | 3 | Msc | YY | 11.20... |
| 7 | 2 | 3 | Msc | ZZ | 11.10... |
| 6 | 2 | 2 | Foo | UU | 10.40... |
| 5 | 2 | 2 | Foo | II | 10.30... |
| 4 | 2 | 2 | You | QQ | 10.20... |
| 3 | 2 | 2 | You | WW | 10.10... |
| 2 | 2 | 3 | Msc | LL | 10.00... |
| 1 | 2 | 4 | CSS | VV | 10.30... |
| 0 | 2 | 4 | CSS | NN | 10.20... |
I realised that, created_at and ids are in the same order. So, I decided using id desc, as I may have trouble arranging date integers in created_at strings. This is what I tried:
$comment = Comment::where('receiver_id', Auth::user()->id)
->orderBy('id','desc')
->groupBy('sender_id', 'title')
->paginate(5);
dd($comment);
The result I want to get is this:
0 => Comment { id = 13, sender_id = 5, title = Art, body = DD } (latest created_at)
1 => Comment { id = 9, sender_id = 3, title = Msc, body = XX }
2 => Comment { id = 6, sender_id = 2, title = Foo, body = UU }
3 => Comment { id = 4, sender_id = 2, title = You, body = QQ }
4 => Comment { id = 1, sender_id = 4, title = CSS, body = VV }
However this is showing something like:
0 => Comment { id = 10, sender_id = 5, title = Art, body = AA }
1 => Comment { id = 5, sender_id = 2, title = Foo, body = II }
2 => Comment { id = 3, sender_id = 2, title = You, body = WW }
3 => Comment { id = 2, sender_id = 3, title = Msc, body = LL }
4 => Comment { id = 0, sender_id = 4, title = CSS, body = NN }
It's giving the latest comments but gets the earliest one.
It's completely skipping User User-3 and shows User-2's 2x subject
Then it shows User-3 here, after finishing User2's 2x subjects instead of after 'Art'.
When I remove ->paginate() and try ->toSql() in my above query, I receive:
"select * from `messages` where `receiver_id` = ? group by `sender_id`, `title` order by `id` desc"
I think the order by id desc part is getting created in the wrong position, so this may cause the problem. But, I'm not sure.
Also, when I swap the places of ->orderBy() and ->groupBy like ->groupBy() first, then ->orderBy(), it returns exactly same result.
Lastly, I tried replacing ->orderBy('title', 'sender_id'), but again same result.
What I am doing wrong or missing out? Thanks in advance.
You group by id and title, so this is normal output
1 => Comment { id = 5, sender_id = 2, title = Foo, body = II }
2 => Comment { id = 3, sender_id = 2, title = You, body = WW }
You seem to work with different queries all the time...
$comment = Comment::where('receiver_id', Auth::user()->id)
->orderBy('id','desc')
->groupBy('sender_id', 'title')
->paginate(5);
// And this
"select * from `messages` where `to` = ? group by `from_id`, `subject` order by `id` desc"
@bobbybouwmann The issue is exactly what you mentioned. But, when I run this code with ->toSql():
$comment = Comment::where('receiver_id', Auth::user()->id)
->orderBy('id','desc')
->groupBy('sender_id', 'title')
->toSql();
it's returning
"select * from `comments` where `receiver_id` = ? group by `sender_id`, `title` order by `id` desc"
And that's the issue
Is your Comment model using the messages table?
@bobbybouwmann Sorry, mistakenly I put other code. I edited my question*.
My question is about mysql group by is executed before order by. (like these ones:
I tried this SO answer, but doesn't work in my case :/
I couldn't figure out how to get the result I want. I want to group comments, and every comment (from same user) should represent only 1 array (0,1,2) as the most recent is 0th in array (so ids as descending or created_at as descending) and should display value of the latest entry in the db (most recent comment).
Just group them by user(sender_id) and order them by created_at in a descending way
$comment = Comment::where('receiver_id', Auth::user()->id)
->groupBy('sender_id')
->orderBy('created_at','desc')
->get();
Please or to participate in this conversation.