获取与数据透视表中的语句匹配的最新记录作为构建器查询

Question

我有以下表格：

users

| id | name  |
--------------
| 1  | user1 |
| 2  | user2 |
| 3  | user3 |
| 4  | user4 |
| 5  | user4 |

user_tasks

| id  | user_id | task    | created at         |
---------------------------------------------------
| 1   | 1       | read_book   | 2020-08-04 00:00:00 |
| 2   | 1       | send_mail   | 2020-08-05 00:00:00 |
| 3   | 2       | read_book   | 2020-08-04 00:00:00 |
| 4   | 2       | send_mail   | 2020-08-05 00:00:00 |
| 5   | 3       | read_book   | 2020-08-05 00:00:00 |

statuses

| id  | name
--------------
| 1   | todo
| 2   | doing
| 3   | reviewing
| 4   | done

数据透视表：

user_task_statuses

| id  | task_id | status_id    | created_at        |
---------------------------------------------------
| 1   | 1       | 1            | 2020-08-04 00:00:00 |
| 2   | 2       | 2            | 2020-08-05 00:00:00 |
| 3   | 2       | 4            | 2020-08-05 00:00:00 |
| 5   | 3       | 1            | 2020-08-04 00:00:00 |
| 6   | 4       | 2            | 2020-08-05 00:00:00 |
| 7   | 5       | 1            | 2020-08-05 00:00:00 |
| 7   | 5       | 2            | 2020-08-05 00:00:00 |

我正在尝试接收以下用户：任务的最新状态为正在执行。通过以下构建器查询，我获取了过去具有 doing 状态的所有任务，但我只想检索当前/最新状态为 doing 的行。在构建器查询中会是什么样子？

return $query->whereHas('userTasks', function (Builder $query) use ($statuses) {
    return $query->whereHas('userTasksStatuses', function (Builder $query) use ($statuses) {
        return $query->whereIn('status_id', $statuses);
    });
});

数据透视表：

public function userTasksStatuses()
{
    return $this->belongsToMany(Status::class, 'user_task_statuses')->withPivot('created_at');
}

我想我有类似的 SQL 工作，但这仅限于一项任务，我不确定如何将其转换为构建器查询。

SELECT 
    * 
FROM 
    user_task_statuses
WHERE task_id = 1
AND status_id = (SELECT MAX(status_id) FROM user_task_statuses WHERE task_id = 1);

Answer 1

您需要按您现在订购的同一字段进行排序，但按降序排列。举个例子，如果你有一个上传完成时的时间戳，称为 upload_time，你会这样做；

return $query->whereIn('status_id', $statuses)->orderBy('upload_time', 'DESC')->first();

Answer 2

设法像这样解决它：

return $query->whereHas('userTasks.userTasksStatuses', function (Builder $query) use ($statuses) {
    $query->whereRaw('user_task_statuses.id = (SELECT MAX(id) FROM user_task_statuses WHERE task_id = user_tasks.id)');
    $query->whereIn('status_id', $statuses);
});