获取Laravel中每个用户的最新消息(行) [英] Get latest message (row) per user in Laravel

问题描述

TL; DR:需要每个发件人的最新消息.

TL;DR: Need latest message from each sender.

在我的Laravel应用程序中，我有两个表:

In my Laravel application I have two tables:

用户:

• id
• 名称

消息:

• id
• sender_id
• recipient_id
• 身体
• created_at

当然还有模型.

用户模型:

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

消息模型:

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.

因此，如果有消息:

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

然后，标识为1(recipient_id)的用户应只看到标识为2和4的消息.

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();

这是可行的，但是我在徘徊，如果有可能实现这种Laravel方式.可能是急切的加载.我的Laravel技能还不够，尝试了几天后，我没有解决方案.

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.

谢谢.

推荐答案

从这篇文章中获得灵感这样做的方式将是这样的:

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();

虽然它不是最Laravel友好的，但它是基于性能的最佳解决方案，如上面此答案中链接的文章所强调的那样

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

这篇关于获取Laravel中每个用户的最新消息(行)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！