Как в Yii2 сделать выборку с подсчетом суммы в связанной таблице?

[Arni]

Есть 2 таблицы: user и transaction. Связь один ко многим (1 пользователь – много транзакций). В таблице transaction есть поле 'amount' и связывающее поле с таблицей user - user_id.

Нужно сделать выборку пользователей и подгрузить связанную таблицу transaction. При этом в свойстве amount таблицы transaction должна быть сумма всех строк 'amount' каждого отдельного пользователя. Условие - не отбирать пользователей у которых сумма по полю 'amount' равна 0.

Пример sql:

Код: Выделить всё

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

Нужно плучить ActiveQuery, т.е.:

Код: Выделить всё

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

[Alexum]

Зачем вам подсчитывать сумму если вы говорите, что amount уже её включает? Или в фразе "При этом в свойстве amount таблицы transaction должна быть сумма всех строк 'amount'" подразумеваете модель? Если нужны только пользователи с ненулевыми транзакциями, то достаточно:

Код: Выделить всё

// "transactions" - метод getTransactions() со связью в модели Users 
$users = User::find()->joinWith(['transactions'])->where(['!=','amount',0])->all();

[Arni]

В таблице transactions для каждого пользователя содержится несколько полей "amount". Т.е. предварительно нужно подсчитать общую сумму и результат подставить в условие выборки "если общая сумма != 0, значит отбирать этого пользователя иначе нет".
Основная сложность в том, как после подсчета общей суммы в связывающей таблице transaction, подставить это значение в условие по User.

[Alexum]

Можно без подсчёта, но запросов будет несколько.

Код: Выделить всё

// "transactions" - метод getTransactions() со связью в модели Users 
$users = User::find()->joinWith(['transactions'],false)->where(['!=','amount',0])->with(['transactions'])->all();;

1. Сцепляются все пользователи с транзакциями, которые больше 0. Для joinWith() жадную загрузку отключаем, т.е. получим только данные по пользователям.
2. К найденным пользователям с ненулевыми транзакциями через with() подтягиваются все транзакции, включая нулевые.

Однако данный способ не подойдёт, если amount отдельной транзакции может быть отрицательным числом.

[andku83]

1. Сцепляются все пользователи с транзакциями, которые больше 0. Для joinWith() жадную загрузку отключаем, т.е. получим только данные по пользователям.

Код: Выделить всё

->joinWith(['transactions'],false)->where(['!=','amount',0])->with(['transactions'])

->joinWith(['transactions'])->where(['!=','amount',0])

оба варианта выше делают одно и то же.

Код: Выделить всё

Для того чтобы получить фильтрацию в связанных данных необходимо делать по-другому:

Код: Выделить всё

->joinWith(['transactions' => function($q) {
    $q->andOnCondition(['!=','amount',0])
}])

Для того чтобы не учитывать отрицательные значения достаточно использовать " > 0".


Ну и по-моему это все не по теме вопроса, если я правильно понял то нужно получить пользователей с положительным балансом и их баланс:
добавить в модель User: public $balance; // для того чтобы модель не игнорировала данные пришедшие от БД

Код: Выделить всё

User::find()->select([User::tableName().'.*', 'balance' => new \yii\db\Expression("SUM(amount)")])
      ->innerJoinWith(['transactions' => function($q){
           $q->andWhere(['>', 'amount', 0])    // возможно вместо `andWhere` понадбится `andOnCondition`
      }], false)

если планируется работать с транзакциями то убрать false из joinWith
данные о суммарном балансе будут в $user->balance

[Arni]

Однако данный способ не подойдёт, если amount отдельной транзакции может быть отрицательным числом.

Да, проблема как раз в том, что в поле 'amount' может быть отрицательное число. Поэтому нужно отобрать пользователей только тех, у которых общая сумма по столбцу 'amount' не будет равна 0. Т.е. без подсчета суммы никак. Но сам подход я взял на заметку, спасибо, пригодится.

[Arni]

Код: Выделить всё

->joinWith(['transactions'],false)->where(['!=','amount',0])->with(['transactions'])
->joinWith(['transactions'])->where(['!=','amount',0])

оба варианта выше делают одно и то же.

Работает, но как-то странно со 2-м вариантом. Вроде бы where() должно касаться таблицы 'user', а не 'transactions'.

В примере

Код: Выделить всё

User::find()->select([User::tableName().'.*', 'balance' => new \yii\db\Expression("SUM(amount)")])
      ->innerJoinWith(['transactions' => function($q){
           $q->andWhere(['>', 'amount', 0])    // возможно вместо `andWhere` понадбится `andOnCondition`
      }], false)

почему то выводит всего 1 пользователя, хотя их несколько (даже если условие убрать все равно выводит одного). Странно.

Но в целом задача немного другая:
в поле 'amount' может быть отрицательное число. Нужно отобрать пользователей только тех, у которых общая сумма по столбцу 'amount' не будет равна 0.

[andku83]

Работает, но как-то странно со 2-м вариантом. Вроде бы where() должно касаться таблицы 'user', а не 'transactions'.

Изучайте SQL, where относится к запросу, а не к конкретной таблице

Код: Выделить всё

User::find()->select([User::tableName().'.*', 'balance' => new \yii\db\Expression("SUM(amount)")])
      ->innerJoinWith(['transactions' => function($q){
           $q->andWhere(['>', 'amount', 0])    // возможно вместо `andWhere` понадбится `andOnCondition`
      }], false)

почему то выводит всего 1 пользователя, хотя их несколько (даже если условие убрать все равно выводит одного). Странно.

Скорее всего у тех пользователей которых вы не видите нет положительных транзакций. Но точно без ваших данных ничего сказать невозможно