How to get sum from hasMany in cakePHP 4.x

[yogeshsaroya]

Hello
I have 2 tables
Users and UsersStakes.
UserStakes have stake records (multiple ) for users and have user_id and amount fields.

Now on Users find instead of get all records of UserStakes (hasMany) I want to just get sum of amount.

I tired this
at Users Table

$this->hasMany(‘UserStakes’,[‘select’=>[‘sum’ => ‘SUM(UserStakes.amount)’]]);

but it’s not working.

How to get sum of amount without all records and then count in loop.

Thanks

[KevinPfeifer]

You are mixing 2 different methods together

->hasMany('UserStakes') is used inside your Model definition src/Model/Table/UsersTable.php which basically just connects your users table with the user_stakes table inside CakePHP.

The sum functionality you are looking for is used inside a query instance like in e.g. your UsersController

// src/Controller/UsersController.php

$this->Users->find()
        ->contain(['UserStakes' => function(\Cake\ORM\Query $q){
                return $q
                    ->enableAutoFields()
                    ->select([
                        'sum' => $q->func()->sum('UserStakes.amount'),
                    ]);
            }]);

The ->enableAutoFields() is needed so not only the sum field gets selected but all the other entity fields of UserStakes as well.

[yogeshsaroya]

@KevinPfeifer Thanks but it’s not working.

first it was showing this error. “You are required to select the “UserStakes.user_id” field(s)”
then i added ‘user_id’ before ‘sum’.

2nd : sum is incorrect. its showing sum of total records from table not for particular users.

here is code

$this->paginate = [
            'contain' => ['UserStakes' => function (\Cake\ORM\Query $q) {
                return $q ->enableAutoFields() ->select(['user_id','sum' => $q->func()->sum('UserStakes.taken_balance')]); }],
            'limit' => 100,
            'fields'=>['Users.id'],

            'order' => ['id' => 'desc']
        ];

/* I replaced amount to taken_balance field */

Now here after using this query, result set have only “user_stakes” data only for one user record, I have data for other users but it’s showing empty “user_stakes” .

2nd : as you mentioned " The ->enableAutoFields() is needed so not only the sum field gets selected but all the other entity fields of UserStakes as well."

result set have “user_stakes” only for one user and “user_stakes” have only 1 record ( it should be 1+ ).

here is resultSet

So in result-set
Users #19 have empty “user_stakes” that is wrong.
Users 18 have only one record ( 0 index only ) for “user_stakes” >
a) that is wrong it should have 3 records (right now i have total 3 records )
b) sum 265001 is wrong. its total of all record from table not for user_id 18. for user_id #18 it should be 250000.

I attached database table screenshot also.

updated
here is table code

// class UsersTable extends Table
      public function initialize(array $config): void
      {
          $this->addBehavior('Timestamp');
          $this->belongsTo('Countries');
          $this->hasMany('UserStakes');
        }

Thanks you so much for help

[KevinPfeifer]

before I try to read into your whole situation can you just give an example of a working SQL Statement you try to reproduce in CakePHP?