mysql - Cakephp 查找 ('list' ) 与汇总数据返回空值

Question

我在 Cakephp 2.4 模型中有以下查询:

$scores = $this->WorkInfo->find('list', array(
'conditions' => array('WorkInfo.work_id' => $work_ids),
'fields' => array('WorkInfo.date', 'SUM(WorkInfo.score)'),
'group' => array('WorkInfo.date')
));

生成以下查询:

SELECT 
  `WorkInfo`.`date`, 
   SUM(`WorkInfo`.`score`) 
FROM 
   `home`.`work_infos` AS `WorkInfo` 
WHERE 
   `WorkInfo`.`work_id` IN (4, 7, 8, 12, 9, 11, 13, 10, 14, 6, 5) 
GROUP BY 
   `WorkInfo`.`date`

我在申请中得到的结果是:

'2014-03-24' => null
'2014-03-25' => null
'2014-03-26' => null
'2014-03-27' => null
'2014-03-28' => null
'2014-03-31' => null

虽然我在 mysql 控制台中粘贴这个查询得到的结果是:

'2014-03-24' => 0
'2014-03-25' => 36
'2014-03-26' => 0
'2014-03-27' => 164
'2014-03-28' => 0
'2014-03-31' => 0

这是怎么回事？应该是相同的查询输出相同的结果，不是吗？

我已经阅读了一些关于为此创建虚拟字段的内容，但我不想矫枉过正，应该可以使用 find 函数通过 Cakephp 执行简单的聚合查询。

谢谢!

Answer 1

试试这个

$scores = $this->WorkInfo->find('all', array(
          'conditions' => array('work_id' => $work_ids),
          'fields' => array('date', 'SUM(score) AS score'),
          'group' => array('date')
      ));

然后用 Set::combine 你可以格式化你的数组 cakephp find list

$scores = Set::combine($scores, '{n}.WorkInfo.date', '{n}.0.score');

打印=>

'2014-03-24' => 0
'2014-03-25' => 36
'2014-03-26' => 0
'2014-03-27' => 164
'2014-03-28' => 0
'2014-03-31' => 0