You need to do in one query count and sum. The query is something like this:

UsersPays::find() ->where([ 'and', ['=', 'users_pays.status', '1'], ['=', 'users_pays.active', '1'], ['=', 'users_pays.pay_type_id', '1'], ]) ->groupBy('users_pays.card_id')

Now the most interesting thing: if I add to the request ->count('users_pays.id') or ->sum('users_pays.money) then everything is fine. but it turns out two requests. yii generates something like this:

 SELECT COUNT(*) FROM (SELECT `users_pays`.* FROM `users_pays` WHERE (`users_pays`.`status` = '1') AND (`users_pays`.`active` = '1') AND (`users_pays`.`pay_type_id` = '1') GROUP BY `users_pays`.`card_id`) `c`

when I write in yii:

  UsersPays::find() ->select('COUNT(users_pays.id) as count, SUM(users_pays.money) as sum') ->where([ 'and', ['=', 'users_pays.status', '1'], ['=', 'users_pays.active', '1'], ['=', 'users_pays.pay_type_id', '1'], ]) ->groupBy('users_pays.card_id')

yii generates the following query:

 SELECT COUNT(users_pays.id), SUM(users_pays.money) FROM `users_pays` WHERE (`users_pays`.`status` = '1') AND (`users_pays`.`active` = '1') AND (`users_pays`.`pay_type_id` = '1') GROUP BY `users_pays`.`card_id`

in the answer comes not the amount and quantity for the whole table, but an array with the amount and quantity for each card_id

Tell me how to do it right?

  • with it without a group by option with ->select(count...) works - Genius Jokes
  • one
    You will first deal with the query, get the correct result in SQL, and then you will port it to yii. Π² ΠΎΡ‚Π²Π΅Ρ‚Π΅ ΠΏΡ€ΠΈΡ…ΠΎΠ΄ΠΈΡ‚ Π½Π΅ сумма ΠΈ количСство ΠΏΠΎ всСй Ρ‚Π°Π±Π»ΠΈΡ†Π΅, Π° массив с суммой ΠΈ количСству ΠΏΠΎ ΠΊΠ°ΠΆΠ΄ΠΎΠΌΡƒ card_id By what you group, by that it groups. If it is necessary on the entire table - remove GROUP BY altogether or make GROUP BY 1 . - Akina

0