Sum of 2 colums? (solved)


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

->contain([‘Users’, ‘Events’])

I expect something like that in the sql request :

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

How can I do that with cake ?


What is the SQL what you get on this query?

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

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'])
        'val1' => $query->func()->sum('Events.value'),
        'val2' => $query->func()->sum('Results.value')
    ->formatResults(function (\Cake\Collection\CollectionInterface $results) {
        return $results->map(function ($row) {
            $row['val'] = $row['val1'] + $row['val2'];

            return $row;

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

The solution is :