Bug: Can't paginate query with group by

[samsonasik]

For example, I have the following SQL:

SELECT `album`.*, 
             COUNT(track.id) as total_song 
             FROM `album` 
                 LEFT JOIN 
            `track` 
                 ON `album`.`id` = `track`.`album_id` 
           GROUP BY `album`.`id`;

For example, I use model(Album::class)->paginate(), the pagination is using query to get total num rows:

SELECT COUNT(*) AS `numrows`
             FROM `album` 
                 LEFT JOIN 
            `track` 
               ON `album`.`id` = `track`.`album_id` 
            GROUP BY `album`.`id`

Which the numrows results multiple rows for getting total rows which make data total rows incorrect.

For workaround, I modify the query with non-group by query:

SELECT 
    *,
   (SELECT count(*) FROM `track` WHERE `album_id` = `album`.`id`) AS total_song 
   FROM `album` 

CodeIgniter 4 version
4.0.2

Expected behavior, and steps to reproduce if appropriate
Show correct total numrows

Context

• OS: OSX
• Web server Apache
• PHP version 7.4.4

[samsonasik]

The query of getting numrows of model()->paginate() when there is a group by needs to be updated like :

select COUNT(*) as numrows from 
      (
            SELECT `album`.*, 
                 COUNT(track.id) as total_song FROM `album` 
                       LEFT JOIN `track` ON `album`.`id` = `track`.`album_id` 
                   GROUP BY `album`.`id`
       ) as album 

[samsonasik]

I've created PR #2777 for it.