How to apply sum in cakephp join table?

79 Views Asked by At

CakePHP version : 3.8 , database : PostgreSQL

I have two tables users and transactions. transactions table has foreign key user_id

I am trying to sum all amount from transactions table corresponding users. I have tried like

$users = TableRegistry::getTableLocator()->get('Users');

$query = $users->find();
$query->select([
    'Users.id','Users.email',$query->func()->sum('Transactions.amount')
]) 
->matching('Transactions')
->group(['Users.id']);

$data = $query->all();

foreach ($data as $key => $value) {
    echo json_encode($value);
}

I am getting output like

{"id":72,"email":"[email protected]","2":null} .... ....

My desire output

{"id":72,"email":"[email protected]","amount":2200}

How can I get my desire result ?

0

There are 0 best solutions below