1

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

Macsh
  • 13
  • 3
  • https://stackoverflow.com/a/58730229/7894379 I think this must be an answer to your question – Ruchita Sheth Apr 30 '22 at 10:01
  • @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! – Macsh Apr 30 '22 at 10:12
  • 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! – Macsh Apr 30 '22 at 10:44
  • If you want to get a result for each user then you have to remove the condition `where('receiver_id', Auth::user()->id)`. This causes the return of the result of one user. – Ruchita Sheth Apr 30 '22 at 10:55

1 Answers1

1

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();
Ruchita Sheth
  • 840
  • 9
  • 27
  • 1
    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. – Macsh Apr 30 '22 at 11:10
  • Okay got your point. how you want a result.. I'll modify an answer – Ruchita Sheth Apr 30 '22 at 11:12
  • I updated the original question as well as it was ambiguous (sorry about that) – Macsh Apr 30 '22 at 11:13
  • @Macsh updated an answer. – Ruchita Sheth Apr 30 '22 at 11:18
  • 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! – Macsh Apr 30 '22 at 11:33
  • Okay, I had missed your where condition in the previously updated answer. Updated an answer again – Ruchita Sheth Apr 30 '22 at 11:36