Title: Laravel Query: orderBy not working with groupBy (with a joined table)
Tags: mysql;laravel
Question: I'm working on a basic messaging functionnality in Laravel and want to display each user who sent a message to the current logged in user along with the last received message, the problem is that the &quot;orderByDesc&quot; isn't working as it displays the first message instead of the last one.
Here's the query I wrote:
$receivedmessages = 75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0table('messages')
-&gt;join('users', 'users.id', '=', 'messages.sender_id')
-&gt;select('messages.*', 'users.username')
-&gt;where('receiver_id', Auth75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0user()-&gt;id)
-&gt;orderByDesc('messages.created_at')
-&gt;groupBy('receiver_id')
-&gt;get();
Any idea how I can fix that?
Thanks
Comment: @RuchitaSheth Oh I searched for it before asking but did not found that answer.
I just added the line "->whereRaw('messages.id IN (select MAX(messages.id) FROM messages GROUP BY receiver_id)')"
And it did the trick! Thank you!

Comment: Well actually it doesn't quite work as I need to receive multiple results (one per user who sent a message) and this only returns one result.
Back to the drawing board!

Comment: https://stackoverflow.com/a/58730229/7894379 I think this must be an answer to your question

Comment: If you want to get a result for each user then you have to remove the condition   `where('receiver_id', Auth75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0user()->id)`. This causes the return of the result of one user.

Here is the accepted answer: Remove -&gt;where('receiver_id', Auth75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0user()-&gt;id) this condition to get a result of each user, instead of the one you're loggedin with
The trick to achieving the above is to get a max Id from a table and use those Ids in WHERE IN condition
$receivedmessages = 75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0table('messages')
-&gt;join('users', 'users.id', '=', 'messages.sender_id')
-&gt;select('messages.*', 'users.username')
-&gt;whereRaw('messages.id IN (SELECT MAX(messages.id) FROM messages GROUP BY receiver_id, sender_id)')
-&gt;where('receiver_id', Auth75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0user()-&gt;id)
-&gt;orderByDesc('messages.created_at')
-&gt;get();
Comment for this answer: Problem with this is that it always gets the same results instead of getting the results for the loggedin user.
Basically if I remove the whereRaw and keep the where I get all the users who sent messages to the loggedin user no problem but can't get the last received message for this exchange.
But if I remove the where and keep the whereRaw. I get all the users who sent messages to the user with id = 1 even if I'm loggedin with another user.
Comment for this answer: I updated the original question as well as it was ambiguous (sorry about that)
Comment for this answer: The updated answer doesn't work by itself, it displays all the users who sent messages regardless of who's loggedin.
But I added back the "where" condition before the "whereRaw" and now it seems to work pretty well. I get all the users who sent messages to the loggedin user along with the last message received.
Thanks for your patience and your help!
Comment for this answer: Okay got your point. how you want a result.. I'll modify an answer
Comment for this answer: @Macsh updated an answer.
Comment for this answer: Okay, I had missed your where condition in the previously updated answer. Updated an answer again


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 4, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2022:00:04/30/22', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mysql><laravel>', 'Title': 'Laravel Query: orderBy not working with groupBy (with a joined table)', 'ViewCount': 64, 'id': 72067352, 'user_AboutMe': None, 'user_AccountId': 23514472.0, 'user_DisplayName': 'Macsh', 'user_Reputation': 13.0, 'user_Views': 1.0, 'user_id': 17562617.0}}