PHP Eloquent扁平化嵌套的"whereHas"选择器

Question

我的任务是为我工作的一家新公司优化一些旧的php项目。一些api调用需要将近20秒的时间，这太长了，我将问题归结为项目中生成的一些sql。它有几个嵌套的exists'，(我相信(是由以下代码生成的：

return Workstation::whereHas('productionSteps', function ($qp) {
$qp->whereHas('subSteps', function ($qs) {
$qs->whereHas('position', function ($qpr) {
$qpr->where('ProjectId', $this->id);
});
});
})->get();

结果查询为：

SELECT *
from `workstations`
where EXISTS (
select * 
from `productionsteps` 
where `productionsteps`.`UsedWorkStationId` = `workstations`.`Id` 
and exists (
select * 
from `substeps` 
where `substeps`.`ParentStepId` = `productionsteps`.`Id` 
and exists (
select * 
from `positions` 
where `substeps`.`PositionId` = `positions`.`Id` 
and `ProjectId` = "some id"
)
)
);

所以我优化了查询，只使用一个exists

SELECT *
from `workstations`
where EXISTS (
select * 
from `productionsteps`, `substeps`, `positions`
where `productionsteps`.`UsedWorkStationId` = `workstations`.`Id` 
AND `substeps`.`ParentStepId` = `productionsteps`.`Id` 
and `substeps`.`PositionId` = `positions`.`Id` 
and `ProjectId` = "some id"
);

然而，我不确定如何更新php代码。

那么我如何在php中做到这一点呢？

Answer 1

根据文档判断，这将是我的最佳猜测：

return Workstation::whereExists(function($query) {
$query
->join('productionsteps', 'UsedWorkStationId', '=', 'workstations.Id')
->join('substeps', 'ParentStepId', '=', 'productionsteps.Id')
->join('positions', 'PositionId', 'positions.Id')
->where('ProjectId', $this->id);
})->get();

Answer 2

我不知道该怎么做；由于您正在处理许多表，因此我会使用广泛的select：

DB::select("
SELECT *
from `workstations`
where EXISTS (
select * 
from `productionsteps`, `substeps`, `positions`
where `productionsteps`.`UsedWorkStationId` = `workstations`.`Id` 
AND `substeps`.`ParentStepId` = `productionsteps`.`Id` 
and `substeps`.`PositionId` = `positions`.`Id` 
and `ProjectId` = ?
);
", ['some id']); // array is for prepared parameters binding