CODEWITHSUNDEEP
phpmysqlsqlyii2
O Connor
O Connor

Reputation: 4392

From SQL select sum group by to active record in Yii framework 2

Below is my pure SQL query.

SELECT SUM(money) AS total_money, user_id 
FROM User 
INNER JOIN Person 
        ON Person.user_id = User.user_id 
GROUP BY user_id

How can I convert this pure query into ActiveRecord in Yii framework 2? I could solve the INNER JOIN but don't know how to solve SUM including GROUP BY with Yii 2 ActiveRecord

Upvotes: 2

Views: 5702

Answers (2)

user7641341
user7641341

Reputation: 209

$atsum =Atttransactions::find()->select([new \yii\db\Expression('SUM(attt_for_receive) as for_receive')]) ->addselect([new \yii\db\Expression('SUM(attt_received) as received')]) ->where(['attt_attc_id'=>$modelTran->attt_attc_id]) ->asArray()->one();

Upvotes: 0

arogachev
arogachev

Reputation: 33548

SELECT part:

use yii\db\Expression;

...

->select([new Expression('SUM(money) as total_money'), 'user_id'])

GROUP BY part:

->groupBy('user_id')

Docs for select() and groupBy() can be found in Query Builder section.

yii\db\Expression is used to prevent quoting.

You can not use sum() method here because it's aggregation method and returns a number, while in your case you use it with other column to return a set of records.

The whole query will look like this:

$personTable = Person::tableName();
$userTable = User::tableName();

$users = User::find()
    ->select([new Expression('SUM(money) as total_money'), 'user_id'])
    ->innerJoin($personTable, "$personTable.user_id = $userTable.user_id")
    ->groupBy('user_id')
    ->all()

A couple more things to mention:

  • It's better to use tableName() method to get actual table name instead of writing it manually. In this case if you decide to change table name, you only need to change it in one place.
  • It's better to replace join part with relation.

Upvotes: 5

Related Questions