Laravel Query: orderBy not working with groupBy (with a joined table)-CodePudding

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 "orderByDesc" isn't working as it displays the first message instead of the last one.

Here's the query I wrote:

$receivedmessages = DB::table('messages')
    ->join('users', 'users.id', '=', 'messages.sender_id')
    ->select('messages.*', 'users.username')
    ->where('receiver_id', Auth::user()->id)
    ->orderByDesc('messages.created_at')
    ->groupBy('receiver_id')
    ->get();

Any idea how I can fix that? Thanks

CodePudding user response：

Remove ->where('receiver_id', Auth::user()->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 = DB::table('messages')
    ->join('users', 'users.id', '=', 'messages.sender_id')
    ->select('messages.*', 'users.username')
    ->whereRaw('messages.id IN (SELECT MAX(messages.id) FROM messages GROUP BY receiver_id, sender_id)')
    ->where('receiver_id', Auth::user()->id)
    ->orderByDesc('messages.created_at')
    ->get();