Get latest message (row) per user in Laravel

Question

TL;DR: Need latest message from each sender.

In my Laravel application I have two tables:

Users:

• id
• name

Messages:

• id
• sender_id
• recipient_id
• body
• created_at

And of course models.

User model:

public function messages() {
    return $this->hasMany('App\Message', 'recipient_id');
}

Messages model:

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

public function recipient() {
    return $this->belongsTo('App\User', 'recipient_id');
}

When user opens his inbox, he should see list of latest message from any other user.

So if there are messages:

id sender_id recipient_id body created_at
1, 2,        1,           hi,  2016-06-20 12:00:00
2, 2,        1,           hi,  2016-06-21 12:00:00
3, 3,        1,           hi,  2016-06-20 12:00:00
4, 3,        1,           hi,  2016-06-21 12:00:00

Then user with id 1 (recipient_id) should see only messages with id 2 and 4.

This is current solution in Users model:

return Message::whereIn('id', function($query) {
                $query->selectRaw('max(`id`)')
                ->from('messages')
                ->where('recipient_id', '=', $this->id)
                ->groupBy('sender_id');
            })->select('sender_id', 'body', 'created_at')
            ->orderBy('created_at', 'desc')
            ->get();

This is working, but I was wandering if it is possible to achieve this the Laravel way. Probably with eager loading. My Laravel skills just not enough and after several days of trying I don't have a solution.

Thanks.

Answer 1

Taking inspiration from this post, the most efficient way to do this would be like so:

DB::table('messages AS m1')
    ->leftjoin('messages AS m2', function($join) {
        $join->on('m1.sender_id', '=', 'm2.sender_id');
        $join->on('m1.id', '<', 'm2.id')
    })->whereNull('m2.id')
    ->select('m1.sender_id', 'm1.body', 'm1.created_at')
    ->orderBy('m1.created_at', 'm1.desc')->get();

While it is not the most Laravel friendly, it is the best solution based on performance as highlighted by the post linked in this answer above

Answer 2

Why not simply accessing the messages, like this -

// get the authenticated user
$user = \Auth::user(); 

// find the messages for that user
return User::with('message')->find($user->id)->messages;