我正在尝试使用CakePHP buildStatement函数执行以下嵌套的select语句，但没有成功。
select * from
(
select i.id, i.name Item, sum(t.qty) Total, l.name Location
from transactions t
left join locations l on (l.id = t.location_id)
left join items i on (i.id = t.item_id)
where t.item_id = 855
group by location_id )
filter where total <> 0
我希望一些专家能帮上忙！
我的尝试羞愧：
$subqueryOptions = array(
'fields' => array('Item.id', 'Item.name', 'SUM(Transaction.qty) total', 'Location.name'),
'conditions' => array(
'Transaction.item_id'=>855,
),
'joins'=>array(
'Location',
'Item'
),
'group'=>array(
'Transaction.location_id'
),
'table'=>'transactions Transaction'
);
$db = $this->Transaction->getDataSource();
$subQuery = $db->buildStatement($subqueryOptions, $this->Transaction);
$res = $this->Transaction->find('all', array(
'fields'=>$subQuery,
'conditions' => array('total !='=>0)
));`
结果声明：
SELECT `SELECT Item.id`, `Item`.`name`, SUM(`Transaction`.`qty`) total, `Location.name
FROM transactions Transaction AS Location Item
WHERE `Transaction`.`item_id` = 855
GROUP BY `Transaction`.`location_id``
FROM `biruni-inventory`.`transactions` AS `Transaction`
LEFT JOIN `biruni-inventory`.`transaction_types` AS `TransactionType`
ON (`Transaction`.`transaction_type_id` = `TransactionType`.`id`)
LEFT JOIN `biruni-inventory`.`item_conditions` AS `ItemCondition`
ON (`Transaction`.`item_condition_id` = `ItemCondition`.`id`)
LEFT JOIN `biruni-inventory`.`locations` AS `Location`
ON (`Transaction`.`location_id`= `Location`.`id`)
WHERE `total` != 0
这完全无关！
这是我能做的最好的事情来构建我的子查询，这里是：
$this->loadModel('MyDBSchema.Item');
$db = $this->Transaction->getDataSource();
$db2 = $this->Item->getDataSource();
$subqueryOptions = array(
'fields' => array('`Item`.`id` Item_id', '`Item`.`name` item_name', 'SUM(`Transaction`.`qty`) total_count', '`Location`.`name` location_name'),
'conditions' => array(
'Transaction.item_id'=>array(855, 901),
),
'joins'=>array(
'left join '.$db->fullTableName($this->Transaction->Location) .' `Location` on (`Location`. `id` = `Transaction`.`location_id`)',
'left join ' .$db2->fullTableName($this->Item). ' `Item` on (`Item`.`id` = `Transaction`.`item_id`)',
),
'group'=>array(
'Transaction.location_id'
),
'table'=>$db->fullTableName($this->Transaction),
'alias'=>'Transaction'
);
$subQuery = $db->buildStatement($subqueryOptions, $this->Transaction);
$subQueryExpression = $db->expression($subQuery);
$mainQueryOptions = array(
'fields'=>array('item_name','total_count','location_name'),
'conditions' => array(
'total_count <>'=>0,
),
'table'=>'('.$subQueryExpression->value.')',
'alias'=>'Item'
);
$mainQuery = $db->buildStatement($mainQueryOptions, $this->Transaction);
$res = $this->Transaction->query($mainQuery);
debug($res); die;
结果是：
array(
(int) 0 => array(
'Item' => array(
'item_name' => 'الجزيرة.. نظرة رمادية إلى أفريقيا',
'total_count' => '4',
'location_name' => '003-D'
)
),
(int) 1 => array(
'Item' => array(
'item_name' => 'أصحاب الحق',
'total_count' => '2',
'location_name' => '003-H'
)
)
)
这对我来说很好。我希望如果有人有更好的解决方案，请告诉我们！
感谢