Yii Framework Forum: Relation hasOne and sum problem - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Relation hasOne and sum problem

#1 User is offline   Apy 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 19-February 12

Posted 02 December 2017 - 11:57 AM

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
0

#2 User is offline   Apy 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 19-February 12

Posted 02 December 2017 - 03:44 PM

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');
}

0

#3 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,410
  • Joined: 16-February 11
  • Location:Japan

Posted 02 December 2017 - 05:42 PM

Check the following section of the guide:

Active Record > Selecting extra fields
http://www.yiiframew...ng-extra-fields
0

#4 User is offline   Apy 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 19-February 12

Posted 03 December 2017 - 02:04 AM

View Postsoftark, on 02 December 2017 - 05:42 PM, said:

Check the following section of the guide:

Active Record > Selecting extra fields
http://www.yiiframew...ng-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?
0

#5 User is offline   alrazi 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,481
  • Joined: 08-August 12
  • Location:Durban, South Africa

Posted 03 December 2017 - 07:21 AM

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
0

#6 User is offline   Apy 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 19-February 12

Posted 03 December 2017 - 10:08 AM

View Postalrazi, on 03 December 2017 - 07:21 AM, said:

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:
0

#7 User is offline   alrazi 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,481
  • Joined: 08-August 12
  • Location:Durban, South Africa

Posted 03 December 2017 - 03:18 PM

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

#8 User is offline   Apy 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 19-February 12

Posted 04 December 2017 - 02:51 AM

View Postalrazi, on 03 December 2017 - 03:18 PM, said:

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?
0

#9 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,410
  • Joined: 16-February 11
  • Location:Japan

Posted 04 December 2017 - 06:10 AM

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;

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users