pagination breaks when using a group in the query builder

hello,

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:

http://pastebin.com/eH3Wre0p



2.0k

nobody any idea?



542
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:

$builder
    ->columns(array(
        'Url.*',
        'COUNT(gData.id) as data_count_total',
        'SUM(if(gData.good = 1, 1, 0)) AS data_count_good',
        'ROUND((SUM(if(gData.good = 1, 1, 0)) / COUNT(gData.id)) * 100, 2) AS data_good_percentage',
        'Categories.name as category'
    ))
    ->from('Url')
    ->groupBy('Url.category_id, Url.params')
    ->join('Categories', 'Url.category_id = Categories.id')
    ->leftJoin('Data', 'Url.id = 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(
    array(
        "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(
      array(
         "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: https://github.com/phalcon/cphalcon/issues/2065