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

I found this solution in another forum, I think that is what you were looking for. I post it so it can be useful for other users

        DB::select('
            SELECT t1.*
            FROM messages AS t1
            INNER JOIN
            (
                SELECT
                    LEAST(sender_id, recipient_id) AS user_id,
                    GREATEST(sender_id, recipient_id) AS recipient_id,
                    MAX(id) AS max_id
                FROM messages
                GROUP BY
                    LEAST(sender_id, recipient_id),
                    GREATEST(sender_id, recipient_id)
            ) AS t2
                ON LEAST(t1.sender_id, t1.recipient_id) = t2.sender_id AND
                   GREATEST(t1.sender_id, t1.recipient_id) = t2.recipient_id AND
                   t1.id = t2.max_id
                WHERE t1.sender_id = ? OR t1.recipient_id = ?
            ', [auth()->guard('api')->user()->id, auth()->guard('api')->user()->id]);

original post: https://laracasts.com/discuss/channels/laravel/get-the-latest-message-of-chat-model-with-mysql-just-cannot-get-the-idea-how-to-do-this?page=1#reply=392529

Answer 2

I like a simpler approach which is mentioned here.

In your User model additionally to existing messages() relationship, add this relationship

public function latestMessage() 
{
    return $this->hasOne(Message::class, 'recipient_id')->latest();
}

Then when you query simply query like this.

$messages = User::with('latestMessage')->get();

$messages contains latest message per user.

Edit

In order to order the result by datetime/id you could do it like this.

$messages = User::with(['latestMessage' => function($message) {
    $message->orderBy('id', 'desc');
}])->get();

$messages contains latest message per user ordered by id. Refer this answer

Answer 3

This is the most "Eloquent way" I have found of doing this:

In User model:

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

public function latestMessagePerSender()
{
    return $this->messages()
        ->whereNotExists(function ($query) {
            $query->from('messages AS m2')
                ->whereRaw('m2.sender_id = messages.sender_id')
                ->whereRaw('m2.created_at > messages.created_at');
    });
}

Then just $user->latestMessagePerSender