我有两个模型用户和任务,它们之间存在多对多关系,任务与自身作为父子关系具有自嵌套关系,
现在我想要获取当前空闲的用户,即没有分配任务或分配的任务状态为已完成或已关闭,下面是我编写的代码和它的 toSql,但我仍然让用户的任务具有其他状态,例如 -进展和审查不足
$freeEmployees = User::where('role', 'employee')->where(function ($q) {
$q->doesntHave('task')
->orWhereHas('task', function ($q1) {
$q1->where('status', config('taskstatus.completed'))
->orWhere('status', config('taskstatus.closed'));
});
})->get()->take($limit);
SELECT
*
FROM
`users`
WHERE
`role` = 'employee' AND(
NOT EXISTS(
SELECT
*
FROM
`task_checklist`
INNER JOIN `user_task` ON `task_checklist`.`id` = `user_task`.`task_id`
WHERE
`users`.`id` = `user_task`.`user_id` AND `task_checklist`.`archived_at` IS NULL
) OR EXISTS(
SELECT
*
FROM
`task_checklist`
INNER JOIN `user_task` ON `task_checklist`.`id` = `user_task`.`task_id`
WHERE
`users`.`id` = `user_task`.`user_id`
AND(
`status` = 'Completed' OR `status` = 'Closed')
AND `task_checklist`.`archived_at` IS NULL
)
)
ORDER BY
`updated_at`
DESC
千巷猫影