[FIXED] Migration cakephp 2.x to 3.x grouped result from query

Issue

I coded a while with cakephp2. I uses a table with calculated values per year and month. e.g.

YEAR | MONTH | VALUENAME | VALUE 
2011 | 1     | value1    | 100
2011 | 2     | value1    | 120
2011 | 1     | value2    | 1234
2011 | 2     | value2    | 4342
etc.

Now I want to have the yearly sum values per 'valuename'. In Cake2 I used the following expression to get the result, where I got the year as array-key (which is important for my view):

$yearvalues = $this->Aggregation->find('list', [
"fields" => ['Aggregation.value_name', 'Aggregation.sum_value', 'Aggregation.y'],
"group" => ['Aggregation.y', 'Aggregation.value_name']
]);

In my Model 'Aggregation' I got the following:

public $virtualFields = array(
        'sum_value' => "sum(value)",
    );

The result is looking as follows:

Array
(
    [2011] => Array
        (
            [value1] => 1429.00
            [value2] => 1180.00
            [value3] => 0.00
            [value4] => 804.00
        )

    [2012] => Array
        (
            [value1] => 1878.00
            [value2] => 2793.00
            [value3] => 3900.00
            [value4] => 1606.00
        )
    ...
)

How do I get the same result now in Cake3. I'm a little bit confused about how to treat the Query-Object in the right way :-( That's what I got up to now:

$yearvalues->select(['Aggregation.value_name', 
'sum_value' => $yearvalues->func()->sum('Aggregation.value'), 'y'])
->group(['Aggregation.y', 'Aggregation.value_name']);

Can someone help me with the right approach?

thx Oliver

Solution

You can use the list finder for this in CakePHP 3.x too, it supports similar options.

$yearvalues
    ->find('list', [
        'keyField' => 'value_name',
        'valueField' => 'sum_value',
        'groupField' => 'y'
    ])
    ->select([
        'Aggregation.value_name', 
        'sum_value' => $yearvalues->func()->sum('Aggregation.value'),
        'Aggregation.y'
    ])
    ->group([
        'Aggregation.y',
        'Aggregation.value_name'
    ]);

The list finder basically just adds a result formatter that will use Collection::combine() to format the retrieved results, which is also why you do not use aliases for the field options, in the results there will only be entities/arrays with properties/field name indices!

See also

• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Finding Key/Value Pairs
• Cookbook > Collections > Iterating > Collection::combine()

Answered By - ndm