Pagination totalCount query returns wrong SQL

Yii 2.0 General Discussions
1

I have a wierd problem. My pagination query (after 2.0.0 to latest Yii version) SQL returns a nested "FROM(SELECT)" query.

This is my code:


// Query (1)

$query1 = (new Query())->select('reservationId, MAX(dateStayed) AS dateStayed')

                                ->from('reservation_nights')

                                ->groupBy('reservationId')

                                ->orderBy(['MAX(dateStayed)' => SORT_DESC]);


// Pagination for query (1)

$countQuery     = clone $query1;

$pages          = new Pagination(['totalCount' => $countQuery->limit(1)->count('reservationId'), 'pageSize' => self::PAGE_SIZE]);

The SQL it generates is:


SELECT COUNT(reservationId) FROM (SELECT `reservationId`, MAX(dateStayed) AS dateStayed FROM `reservation_nights` GROUP BY `reservationId` ORDER BY MAX(dateStayed) DESC LIMIT 1) `c`

Shouldn’t it be?:


SELECT COUNT(reservationId) FROM `reservation_nights` GROUP BY `reservationId` ORDER BY MAX(dateStayed) DESC LIMIT 1

Thanks for the help.

2

No.

Your query do not calculate total count.

3

Wierd thing is that if I remove ->groupBy(‘reservationId’) it works. My table needs to group on “reservationId” since it contains multiple records but I want to count the unique ones.

Isn’t this possible with this approach?

Thank you