[FIXED] CakePHP 3 : how to make a sum of 2 columns

Issue

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

Solution

What you're doing there is adding two objects on PHP level, that will cause the objects to be casted to integers, which surely won't work and fail with an error.

You need to use the expression builder to build an expression that produces the desired SQL, just add your function expressions and tie/conjugate them with the + operator. Also you cannot access $query before it has been defined, and I'd suggest to use identifier expressions for the colum names that you're passing to sum():

use Cake\Database\Expression\IdentifierExpression;

// ...

$query = $this->Results->find();
$query
    ->contain(['Users', 'Events'])
    ->select([
        'user_id' => 'Users.id',
        'val' => $query
            ->newExpr()
            ->add($query->func()->sum(new IdentifierExpression('Events.value')))
            ->add($query->func()->sum(new IdentifierExpression('Results.value')))
            ->tieWith('+') // setConjunction() as of CakePHP 3.4
    ])
    ->group(['user_id']);

See also

• API > \Cake\Database\Expression\QueryExpression

Answered By - ndm