Sum of 2 colums? (solved)

Need Help
1

Hello

I have a problem with a sql request, I want to sum 2 columns :
This syntax do not works …

$query=$this->Results->find()
->contain([‘Users’, ‘Events’])
->select([
‘user_id’=>‘Users.id’,
‘val’=>$query->func()->sum(‘Events.value’)+$query->func()->sum(‘Results.value’)
])
->group([‘user_id’]);

I expect something like that in the sql request :

SUM(Events.value)+SUM(Results.value)) AS `val

How can I do that with cake ?

Thanks

2

What is the SQL what you get on this query?

3

I have :

2 AS `val`

yes with the number 2

With 2 errors, one for each $query->func :
Object of class Cake\Database\Expression\FunctionExpression could not be converted to int

4

A possible solution is to select 2 fields with each their sum function and count them together when fetched.

$query = $this->Results->find()
    ->contain(['Users', 'Events'])
    ->select([
        'user_id'=>'Users.id',
        'val1' => $query->func()->sum('Events.value'),
        'val2' => $query->func()->sum('Results.value')
    ])
    ->group(['user_id'])
    ->formatResults(function (\Cake\Collection\CollectionInterface $results) {
        return $results->map(function ($row) {
            $row['val'] = $row['val1'] + $row['val2'];

            return $row;
        });
    });
5

Thanks for that, but it does not work in my case. I need paginate with sort, and formatResult do not work.

The solution is :
‘val’=>$query
->newExpr()
->add($query->func()->sum(‘Events.value’))
->add($query->func()->sum(‘Results.value’))
->tieWith(’+’)