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());
}
...
public function getWalletBalance()
{
return $this->hasMany(User_wallet::className(), ['user_id' => 'id'])
->select('user_id, SUM(amount) AS amount');
}
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
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;