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;

Answer 3

this may be a solution (not tested though)

User::with([
'messages' => function ($q) {
    $q->select('sender_id', 'body')->groupBy('sender_id')->orderBy('created_at', 'desc');
}
])->find(1);

Answer 4

I tried some similar approach and found out you only need to orderBy created_at immediately you find all the messages of the User then you can use Laravel's Collection to group them them by sender_id. So to my understanding, the following approach should work, i.e give you the last message receive by the user from the senders :

So assuming you have an Authenticated User as $user which in this context is the same as receiver with receiver_id in messages table, then:

$messages = $user->messages()
    ->orderBy('created_at', 'desc')
    ->get(['sender_id', 'body', 'created_at'])
    ->groupBy('sender_id'); //this is collections method

Then loop round the collection, and fetch the first:

$result =  new \Illuminate\Database\Eloquent\Collection ();
foreach ($messages as $message){
    $result->push($message->first()); //the first model in the collection is the latest message
}

You should end up with the result such as:

 Illuminate\Database\Eloquent\Collection {#875
     all: [
       App\Message {#872
        sender_id: "2",
        body: "hi",
        created_at: "2016-06-21 12:00:00",
      },
      App\Message {#873
        sender_id: "4",
        body: "hi",
        created_at: "2016-06-21 12:00:00",
      },
    ]

PS: A note is that I can't say how efficient this might be but one thing is for sure, is the limited number of query on the db.

Hope it helps :)

UPDATE:

I will break it down as I tried it. ~

It fetches all records of messages that belongs to user into a collection (already ordered by created_at) then, using laravel's groupBy() you have a result like the example given in that doc.

This time I didnt convert to Array. Instead, its a collection Of Collections. like collection(collection(Messages))

Then you pick the first Model at each index. The parameters I already passed into the get() method ensures only those fields are present (i.e ->get(['sender_id', 'body', 'created_at']). This is is totally different from mysql groupBy(), as it does not return one row for each group rather, simply groups all records by the given identifier.

ANOTHER UPDATE

I discovered later that calling unique() method on the resulting ordered messages would actually do the job, but in this case the unique identifier would be sender_id. (ref: Laravel's unique)That is:

$messages = $user->messages()
    ->orderBy('created_at', 'desc')
    ->get(['sender_id', 'body', 'created_at'])
    ->unique('sender_id'); //unique replaces `groupBy`

The consequence is that we don't need the foreach and the groupBy we have the result here.

One other way to avoid repeatition (of the above) if this is needed in more than one place is to use Laravel's query scope i.e in Message model we can have something as this:

public function scopeLatestSendersMessages($query) 
{
     return $query->orderBy('created_at', 'desc')
         ->get(['sender_id', 'body', 'created_at'])
         ->unique('sender_id');
}

Then in the controller use:

$messages = $user->messages()->latestSendersMessages();

PS: Still not sure which one is optimally better than the other.

Answer 5

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 6

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 7

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

Answer 8

Maybe you can try this one:

        $user = \Auth::user(); 

        // Get the latest date
        $last_date_created = Message::latest()->first()->created_at; // also check for possible null

        // Get the date only - use to specify date range in the where section in the eloquent query
        $target_date = date('Y-m-d', strtotime( $last_date_created ) );

        // Retrieve the messages
        $latest_posts = Message::where('recipient_id', $user->id)
                               ->where('created_at', '>=', $target_date . ' 00:00:00')
                               ->where('created_at', '<=',  $target_date . ' 23:59:59')
                               ->groupBy('sender_id')
                               ->groupBy('created_at')
                               ->get();

        return $latest_posts;

This may not be very efficient since it took two queries to do the job but you will gain benefits thru code readability.

I hope it works the way it should be. I haven't tested it though but that's how I usually do this... Cheers!

Answer 9

You can try this one

Messages::where('recipient_id',**{USER_ID}**)
          ->group_by('sender_id')
          ->order_by('id','desc')
          ->get();