将SQL查询更改为CakePHP查询

Question

下面给出的是我想在Cakephp中转换的查询，有人能帮我吗。

SELECT S.name, (sum(El.amount) - (EE.total - EE.Returned_equipment)) As "Leftover", EE.equipment_id, sum(EL.amount), EQ.type 
From States S 
Inner Join events EV ON S.id = EV.state_id 
Inner Join equipment_events EE ON EE.event_id = EV.id 
Inner Join equipment_locations EL On EL.equipment_id = EE.equipment_id 
Inner Join equipment EQ On EQ.id = EE.equipment_id 
Inner Join events E2 ON E2.location_id = EL.location_id
Inner Join locations L On L.state_id = S.id
Where EE.Bool_value = "Open" Group BY EE.equipment_id, S.name

Answer 1

在CakePHP 3中尝试以下操作：

use CakeORMTableRegistry;
$query = TableRegistry::getTableLocator()->get('S', ['table' => 'States'])->find();

$sumElAmount = $query->func()->sum('El.amount');

$query->select(['S.name']);
$query->select(['Leftover' => $sumElAmount - 'EE.total - EE.Returned_equipment']);
$query->select(['EE.equipment_id']);
$query->select([$sumElAmount]);
$query->select(['EQ.type']);
$query->where(['EE.Bool_value' => 'Open']);
$query->join([
'EV' => [
'table' => 'events',
'type' => 'INNER',
'conditions' => ['S.id = EV.state_id'],
],
// add more inner joins
]);
$query->group(['EE.equipment_id', 'S.name']);
$this->set('results', $query);

并阅读：

https://book.cakephp.org/3/en/orm/query-builder.html