Yii2: queryScalar - wrong count of rows

[vitaliy-evsyukov]

What steps will reproduce the problem?

Having query with group by and limit:

$query = Products::find()->joinWith('units')->groupBy('id')->limit(10);

What is the expected result?

Total count of all rows

What do you get instead?

Total count of limited rows (10)

COUNT query is:

SELECT COUNT(*) FROM
(SELECT `products`.* FROM `products`
   LEFT JOIN `units` `u` ON `products`.`unit_id` = `u`.`id
   GROUP BY `products`.`id` LIMIT 1
) `c`

instead of

SELECT COUNT(*) FROM 
(SELECT `products`.* FROM `products`
   LEFT JOIN `units` `u` ON `products`.`unit_id` = `u`.`id
   GROUP BY `products`.`id`
) `c`

Additional info

Related issue where code was created: https://github.com/yiisoft/yii2/issues/2490
I think you just need to execute query first and then re-assign select, limit and offset

[klimov-paul]

This is expected result.
You should not set limit for the count query or unset it before like it is done at ActiveDataProvider:
https://github.com/yiisoft/yii2/blob/master/framework/data/ActiveDataProvider.php#L165

[vitaliy-evsyukov]

facepalm.jpg

1. In all places you need to do this routines instead of executing it in one point in framework
2. In framework code you still set limit and offset as null before creating command and re-assign it after creating. You do it for first part of condition

  if (empty($this->groupBy) && empty($this->having) && empty($this->union) && !$this->distinct) {
            return $command->queryScalar();

but I have no idea why you don't want to do the same for the second part.