CODEWITHSUNDEEP
mysqllistcakephpfindsum
patopato
patopato

Reputation: 5

Cakephp find('list') with summarized data returns nulls

I have the following query in a Cakephp 2.4 model:

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

Which generates the following query:

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`

The result I get in my application is:

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

While the result I get from pasting this very query in the mysql console is:

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

What is going on here? It is supposed that same queries output same results, isn't it?

I have read something about creating virtual fields for this, but I do not want to overkill, it should be possible to perform a simple aggregation query through Cakephp using the find function.

Thanks!

Upvotes: 0

Views: 491

Answers (2)

Nunser
Nunser

Reputation: 4522

Ok, sadly, I think what you want to do can't be done as you want to do it.

Let's see, you use the find('list') method, so that's here in the API. Code looks normal, and as you said, query is ok, returns everything you want. Problem is in line 2883

return Hash::combine($results, $query['list']['keyPath'], $query['list']['valuePath'], $query['list']['groupPath']);

That line organizes the returned array after the query is done. And seeing the doc for that function, we have

Creates an associative array using a $keyPath as the path to build its keys, and optionally $valuePath as path to get the values. If $valuePath is not specified, or doesn’t match anything, values will be initialized to null.

Which is what happens to you. Now, debugging, the query result before applying the Hash::combine function is something like this

Array
(
    [0] => Array
        (
            [WorkInfo] => Array
                (
                    [date] => 2013-04-01
                )

            [0] => Array
                (
                    [SUM(`WorkInfo`.`score`)] => 24
                )

        )

)

so you see, you get the results. And the respective Hash::combine

Array
(
    [groupPath] => 
    [valuePath] => {n}.SUM(WorkInfo.score)
    [keyPath] => {n}.WorkInfo.date
)

which probably causes problem with the dot inside the parenthesis. And the combine function doesn't find the valuePath, and you get null, and you get sad.

If you change your query to 'SUM(WorkInfo.score) AS score' (leaving everything as is), you have almost the same problem with valuePath

Array
(
    [groupPath] => 
    [valuePath] => {n}.SUM(WorkInfo.score) as score
    [keyPath] => {n}.WorkInfo.date
)

 //respective result array
Array
(
    [0] => Array
        (
            [WorkInfo] => Array
                (
                    [date] => 2013-04-01
                )

            [0] => Array
                (
                    [score] => 24
                )

        )

)

You might think that doing 'SUM(score) AS score' (without the dot) will solve it, but the code of find('list') adds the alias if it doesn't find a dot (in line 2865).

So... I guess what I'm saying is: do a virtual field, or listen to Isaac Rajaei, or create a custom find function. But with find('list') and SUM() you won't have luck :(

Upvotes: 1

Fury
Fury

Reputation: 4776

Try this

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

then with Set::combine you can format your array cakephp find list

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

prints=>

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

Upvotes: 2

Related Questions