pagination breaks when using a group in the query builder


so i am trying to (left) join 2 tables containing vote information to a content table i am basically trying to join how often a content has been voted and the sum of the value ( upvote means value +1 downvote value -1 ). when i intercept it and look @ $sql it shows me exactly the command i want and when running it on my database it also returns the right information i need to display, however the paginator's $page->before next last etc all are set to 1 .

since formatting looked wierd in the preview i decided to put the code on pastebin:


nobody any idea?

edited Mar '14

I have the same problem:

in the meantime I executed the query before. if you don't have big data you can apply like that and wait for a fix:

        'COUNT( as data_count_total',
        'SUM(if(gData.good = 1, 1, 0)) AS data_count_good',
        'ROUND((SUM(if(gData.good = 1, 1, 0)) / COUNT( * 100, 2) AS data_good_percentage',
        ' as category'
    ->groupBy('Url.category_id, Url.params')
    ->join('Categories', 'Url.category_id =')
    ->leftJoin('Data', ' = gData.url_id', 'gData')
$queries = $builder->getQuery()->execute();
//echo ($builder->getPhql());die;
[email protected] Phalcon\Paginator\Adapter\QueryBuilder using group by breaks pagination and like it is now is slow
//as is executing data before limit and page
$paginator = new Phalcon\Paginator\Adapter\Model(
        "data" => $queries,
        "limit" => 100,
        "page" => $currentPage

another quick/dirty solution for big data would be to override the totals (_items and _pages) after getting the totals count:

$paginator = new Phalcon\Paginator\Adapter\QueryBuilder(
         "builder" => $builder,
         "limit" => 100,
         "page" => $currentPage

$dataCount = $builder->getQuery()->execute()->count();
$page->next = $page->current + 1;
$page->before = $page->current - 1 > 0 ? $page->current - 1 : 1;
$page->total_items = $dataCount;
$page->total_pages = ceil($dataCount / 100);
$page->last = $page->total_pages;

also you can watch the issue here too: