如何按组分组并获取最新的记录

Question

我应该使用哪个Laravel查询来groupBy('author_id')，但保留最新的结果。目前我尝试的所有方法都会显示第一个结果。我已经尝试使用MAX(id)作为id SQL函数，但没有成功。

它可以找到记录，但只能找到组中的第一条记录。

以下是我正在使用的模型查询：

public function lastMessages() {
     return $this->hasMany('App\Message', 'recipient_id')->groupBy('author_id')->orderBy('created_at', 'DESC');
}

我还尝试过以下方法：

public function lastMessages() {
     return $this->hasMany('App\Message', 'recipient_id')
         ->select(DB::raw('*, max(id) as id))
         ->groupBy('author_id')
         ->orderBy('id', 'desc');
}

我也尝试过这个方法，但没有成功：

public function lastMessages() {
     return $this->hasMany('App\Message', 'recipient_id')
         ->select(DB::raw('*, max(created_at) as created_at))
         ->groupBy('author_id')
         ->orderBy('created_at', 'desc');
}

上述所有内容将返回作者组的第一行。我希望返回每个组的最后一行或最新行。我也尝试过 ->latest()，但没有结果！

感谢您提前的帮助。

我的表格：

id project_id author_id recipient_id message created_at updated_at

- max234435

---

如果您展示一些样本数据以及期望的输出，可能会有所帮助。 - Tim Biegeleisen

Answer 1

创建一个子查询连接，以此方式获取每个作者的最新消息 ID，在这种情况下，由于您已经在连接子查询中获取了每个作者的最新消息 ID，因此不再需要使用 group by。在每个组中，您已经获取了最新创建的消息，因此不再需要 max(created)。

public function lastMessages() {
    return $this->hasMany('App\Message', 'recipient_id')
         ->select('*')
         ->join(DB::raw('(Select max(id) as id from messages group by author_id) LatestMessage'), function($join) {
            $join->on('messages.id', '=', 'LatestMessage.id');
            })
         ->orderBy('created_at', 'desc');
}

截图

该链接指向一张截图。

- Beginner

---

天哪！谢谢，我一直在这个问题上纠结了4个小时。我找到了另一个解决方案，而不需要使用join。我也会发布答案。 - max234435

Answer 2

在这个问题上我苦恼了好几个小时，经过这个论坛的帮助，我终于得到了下面的代码，它给出了我想要的结果。

public function lastMessages() {
    return $this->hasMany('App\Message', 'recipient_id')
        ->select('*')
        ->whereIn('id', function($q){
            $q->select(DB::raw('MAX(id) FROM messages GROUP BY author_id'));
        });
}

- max234435

---

是的，但这个查询较慢，因为你在每一行中都调用了这个子查询。 - Beginner

你如何衡量查询的速度？在 SQL 方面我不是最擅长的。 - max234435

在你的查询之前加上“Explain”。 - Beginner

你会在Laravel中如何实现这个？使用DB::Raw还是有一个explain->()命令可用吗？ - max234435

手动输入“解释查询”到您的 DBMS。 - Beginner

Answer 3

要按最新记录分组，您可以使用最新的和唯一的方法，如下所示：

public function lastMessages() {
 return $this->hasMany('App\Message', 'recipient_id')->latest()->get()->unique('author_id');
}

- M.Samiei