Laravel query: orderBy not working with groupBy (using join table)

Question

I am developing basic messaging functionality in Laravel and want to display every user who sent a message to the currently logged in user and the last message received, the problem is that "orderByDesc" is not working properly it displays the first message instead of the last One piece.

This is 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 to solve this problem? Thanks

Answer 1

Remove->where('receiver_id', Auth::user()->id) This condition to get results for each user instead of the one you logged in with

The trick to achieve the above is to get the max Id's from the table and use those Id in a 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();