We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Phalcon\Paginator\Adapter\QueryBuilder with GROUP BY

       $builder = $this->modelsManager->createBuilder();
        $builder->from('Campaigns');
        $builder->join('Clicks', 'Clicks.camp_id = Campaigns.camp_id');
        $columns = array(
            'Campaigns.network AS network',
            'Campaigns.camp_id AS camp_id',
            'Campaigns.camp_name AS camp_name',
            'Campaigns.package_name as package_name',
            'COUNT(Clicks.click_time) AS click_count',
            'COUNT(Clicks.install_time) AS install_count',
            //'(COUNT(Clicks.click_time)/COUNT(Clicks.install_time)) AS install_rate'
        );
        $builder->columns($columns);
        $builder->groupBy('Campaigns.camp_id, Campaigns.network, Campaigns.camp_name, Campaigns.package_name');
        $config = array(
            'builder' => $builder,
            'limit' => 20,
            'page' => $params['page']
        );
        $paginator = new Phalcon\Paginator\Adapter\QueryBuilder($config);
        $page = $paginator->getPaginate();

the $page get the wrong rowcount. and the recorded RAW SQL for rowcount calculating is like below and the result value of it is sure NOT rowcount.

SELECT COUNT(*) AS `rowcount` FROM `campaigns` INNER JOIN `clicks` ON `clicks`.`camp_id` = `campaigns`.`camp_id` GROUP BY `campaigns`.`camp_id`, `campaigns`.`network`, `campaigns`.`camp_name`, `campaigns`.`package_name`

what's wrong?



17.7k

IS Phalcon\Paginator\Adapter\QueryBuilder NOT support groupBy ?

I'm pretty sure it does support groupBy. My guess is just that the query the server sees - when you're using the query builder - isn't what you actually think it is. I suggest doing logging on the MySQL server to see what the query actually is.