0

I am creating an inbox system, and I want to get the last message per conversation.

Message Table

id sender_id sent_to_id   body   created_at          updated_at
1     2          1        hello      2019-06-01 20:20:01   
2     1          2         ok        2019-06-02 23:20:01   
3     3          1         yes       2019-06-01 17:20:01

The result should look like this :

2     1          2         ok        2019-06-02 23:20:01   
3     3          1         yes       2019-06-01 17:20:01   



Message Model 



public function sender()
{
    return $this->belongsTo(User::class, 'sender_id');
}


public function receiver()
{
    return $this->belongsTo(User::class, 'sent_to_id');
}
Bassem Ben Dhaou
  • 1
  • 1
  • It would help folks to answer if you were to make this a little more clear. What does the model look like? Where are you outputting this, what does the code look like that is failing now? It's hard to understand what you are looking for without a little more information – Watercayman Jun 02 '19 at 16:01

1 Answers1

0

From your Message model, you can get the last message like this:

$latestMessage= Message::latest()->first();

If you want to do it direct to the table:

$latestMessage= DB::table('messages')
            ->latest()
            ->first();

If you want to pull from only a specific sender's messages:

$latestMessage= Message::where('sender_id', $someSenderIdVar)->latest()->first();

Either way, you have an object with all of the data you asked for in your question as a result - you can then output it any way you wish:

$latestMessage->id  // Prints 2
$latestMessage->sender_id // prints 2
$latestMessage->body // prints 'ok'

Etc. Move this to your blade file, and you can make a table very easily now that you have your object with the required result.

Watercayman
  • 7,970
  • 10
  • 31
  • 49
  • this show me just the last recived message per conversation , but i want to show last recived or sended message per conversation .... just like facebook inbox – Bassem Ben Dhaou Jun 02 '19 at 16:39
  • "this show me just the last recived message per conversation " That's *exactly* what you asked for. To show last send or received you will have to change the 3rd query from sender to receiver depending on what you want. If last from sender, query exactly as I have it in 3rd query. If you want last received, query by receiver_id in same way. – Watercayman Jun 02 '19 at 16:45
  • the solution : $messages= DB::select( ' SELECT t1.* FROM messages AS t1 INNER JOIN ( SELECT LEAST(sender_id, sent_to_id) AS sender_id, GREATEST(sender_id, sent_to_id) AS sent_to_id, MAX(id) AS max_id FROM messages GROUP BY LEAST(sender_id, sent_to_id), GREATEST(sender_id, sent_to_id) ) AS t2 ON LEAST(t1.sender_id, t1.sent_to_id) = t2.sender_id AND GREATEST(t1.sender_id, t1.sent_to_id) = t2.sent_to_id AND t1.id = t2.max_id WHERE t1.sender_id = ? OR t1.sent_to_id = ? ', [$user_id, $user_id]); – Bassem Ben Dhaou Jun 02 '19 at 23:25