Relation hasOne and sum problem

I am having problem when getting sum column in relation function

Let say I have

Table 1 named: user




id|usermane

1|apple

2|banana

3|coke



Table 2 named: user_wallet

id|user_id|amount




1|1|-100

2|1|500

3|3|200



As I want to get user id 1 data with their wallet remain

So I write the following code

User.php(Model):




...

class User extends BaseUserModel

{

	public function getWalletBalance()

	{

		return $this->hasMany(User_wallet::className(), ['user_id' => 'id'])->sum('amount');

	}

}

...



SiteController.php




...

class actionIndex

{

	Vardumper::dump(User::find()->with('walletBalance')->where([id => 1])->one());

}

...



I expect to have (asArray here)




[

	'id' => 1,

	'username' => 'apple',

	'walletBalance' => [

		'amount' => 400,

	]

]



But I get error message

app\models\User has no relation named "walletBalance".

If I remove ->sum(‘amount’) in User.php, no error and can be run normally.

How to fix it?

Thank you.b

I change some code and it works now

But I think this is not ideal.

Any better suggestions?




public function getWalletBalance()

{

	return $this->hasMany(User_wallet::className(), ['user_id' => 'id'])

	->select('user_id, SUM(amount) AS amount');

}



Check the following section of the guide:

Active Record > Selecting extra fields

http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#selecting-extra-fields

Thanks for reply

and I follow this code and changed a little bit which use SUM(amount as balance), I also get an error message: Indirect modification of overloaded element of app\models\User has no effect




class Customer extends \yii\db\ActiveRecord

{

    /**

     * Defines read-only virtual property for aggregation data.

     */

    public function getOrdersCount()

    {

        if ($this->isNewRecord) {

            return null; // this avoid calling a query searching for null primary keys

        }

        

        return empty($this->ordersAggregation) ? 0 : $this->ordersAggregation[0]['counted'];

    }


    /**

     * Declares normal 'orders' relation.

     */

    public function getOrders()

    {

        return $this->hasMany(Order::className(), ['customer_id' => 'id']);

    }


    /**

     * Declares new relation based on 'orders', which provides aggregation.

     */

    public function getOrdersAggregation()

    {

        return $this->getOrders()

            ->select(['customer_id', 'counted' => 'count(*)'])

            ->groupBy('customer_id')

            ->asArray(true);

    }


    // ...

}


foreach (Customer::find()->with('ordersAggregation')->all() as $customer) {

    echo $customer->ordersCount; // outputs aggregation data from relation without extra query due to eager loading

}


$customer = Customer::findOne($pk);

$customer->ordersCount; // output aggregation data from lazy loaded relation



If I remove asArray, the object return do not have "counted(or my code is balance)" element, I can see customer_id only

Am I miss understanding something for the document?

you need to declare a property in your model to map to your scalar value for example in you case


public $ordersCount; // this should be same as the 'COUNT(id) as ordersCount' 

ideally using virtual attributes or extra fields is the best way about fetching counts or scalar values from database, but here is another way to achieve similar results


<?php


class User extends BaseUserModel

{

// ...

        public function getWallet()

        {

                return $this->hasMany(User_wallet::className(), ['user_id' => 'id']);

        }


        public function getWalletBalance()

        {

        	return $this->getWallet()->sum('amount');

        }

//...

}




class actionIndex

{

	$user = User::find()->where(['id' => 1])->one()

	var_dump($user->walletBalance);

}

note: the above query will not do a join it will run the second query to get the sum

Both of them still return null.

Really don’t know what happens :unsure:

do you have data in your database ? could it be there is no data

I have data in my database

and even no data in my database

the AR class also will return the model, right?

At first, "User" has many "UserWallet"s, right? Then you should already have the following relation declared:




class User extends \yii\db\ActiveRecord

{

...

    public function getUserWallets()

    {

        return $this->hasMany(UserWallet::className(), ['user_id' => 'id']);

    }

}



You have to use this relation.




class User extends \yii\db\ActiveRecord

{

...

    private $_walletBalance;


    public function setWalletBalance($amount)

    {

        $this->_walletBalance = $amount;

    }

    

    public function getWalletBalance()

    {

        if ($this->isNewRecord) {

            return null; // this avoid calling a query searching for null primary keys

        }


        if ($this->_walletBalance === null) {

            $this->setWalletBalance(

                empty($this->userWalletsAggregation) ? 0 : $this->userWalletsAggregation[0]['balance']

            );

        }

        

        return $this->_walletBalance;

    }


    public function getUserWallets()

    {

        return $this->hasMany(UserWallet::className(), ['user_id' => 'id']);

    }


    /**

     * Declares new relation based on 'userWallets', which provides aggregation.

     */

    public function getUserWalletsAggregation()

    {

        return $this->getUserWallets()

            ->select(['user_id', 'balance' => 'sum(amount)'])

            ->groupBy('user_id')

            ->asArray(true);

    }



Then you can write like this:




$users = User::find()

    ->select([

        '{{user}}.*', // select all user fields

        'SUM({{user_wallet}}.amount) AS walletBalance' // calculate wallet balance

    ])

    ->joinWith('userWallets') // ensure table junction

    ->groupBy('{{user}}.id') // group the result to ensure aggregation function works

    ->all();

// Or:

// $users = User::find()->with('userWalletsAggregation')->all();

foreach($users as $user) {

    echo $user->name;

    echo $user->walletBalance;

}


$user = User::findOne($user_id);

echo $user->name;

echo $user->walletBalance;