将SQL查询更改为CakePHP查询

eoxn13cs 于 2022-11-12 发布在 PHP

关注(0)|答案(1)|浏览(178)

下面给出的是我想在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

cakephp

来源：https://stackoverflow.com/questions/64584653/change-sql-query-to-cakephp-query

1条答案

按热度按时间

xzabzqsa1#

在CakePHP 3中，请尝试以下操作：

use Cake\ORM\TableRegistry;

    $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

赞(0）回复(0）举报 2022-11-12

我来回答

将SQL查询更改为CakePHP查询

1条答案

相关问题

热门标签

最新问答