How in Yii2 to make a selection with sum calculation in a linked table?

Question

Yii

There are 2 tables: user and transaction. One-to-many relationship (1 user - many transactions). The transaction table has a 'amount' field and a linking field to the user table - user_id.
You need to make a selection of users and load the associated transaction table. In this case, the amount property of the transaction table should contain the sum of all the 'amount' rows of each individual user. The condition is not to select users whose sum in the 'amount' field is 0.
SQL example:

SELECT * FROM (
        SELECT user.*, (SELECT SUM(amount) FROM transaction WHERE user_id = user.id) AS balance FROM user
    ) t WHERE balance != 0

You need to get ActiveQuery, i.e.:

$usersQuery = User::find()
            ->with(...)

Answer 1

Use query-builder
https://www.yiiframework.com/doc/guide/2.0/en/db-q...