Get latest record that matches statement in pivot table as Builder query-CodePudding

I have the following tables:

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

pivot table:

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 |

I'm trying to receive the users that have tasks that have the doing as its latest status. With the following Builder query I get all tasks that have had a doing status in the past, but I only want to retrieve the rows which current/latest status are doing. How would that look like in a builder query?

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

Pivot table:

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

I think I have something similar working with SQL, but this is limited to one task and I'm not sure how to convert this into a builder query.

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

CodePudding user response：

You'll need to order by the same field you're ordering by now, but descending. As an example, if you have a time stamp when the upload was done called upload_time, you'd do something like this;

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

CodePudding user response：

Managed to resolve it like so:

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