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 querybuilder total_items always returns 1

I make a query via createBuilder and when executing it getQuery()->execute()->toArray() I got 10946 elements. I want to paginate it, so I pass it to:

$paginator = new \Phalcon\Paginator\Adapter\QueryBuilder(array(
            "builder" => $builder,
            "limit" => $limit,
            "page" => $current_page
        ));

$limit is 25 and $current_page is 1, but when doing:

$paginator->getPaginate();

$page->total_items;

returns 1.

Is that a bug or am I missing something?

UPD: Phalcon version is 1.3.2

UPD2: Duplicate on stackoverflow: https://stackoverflow.com/questions/25502262/phalcon-querybuilder-total-items-always-returns-1



98.9k

Could you please post the query builder you're passing to the paginator?



1.7k
edited Aug '14
        $builder = $this->modelsManager->createBuilder()
            ->columns(array(
                'P.id',
                'P.name',
                'P.description',
                'P.sku_id',
                'P.price',
                "GROUP_CONCAT(PImages.filename, '') AS filename",
                "PVideos.url",
                'P.created_at',
                'P.is_active',
            ))
            ->from(array('P' => 'NS\P'))
            ->leftjoin('NS\PImages', 'PImages.P_id = P.id', 'PImages')
            ->leftjoin('NS\PVideos', 'PVideos.P_id = P.id', 'PVideos')
            ->groupBy('P.id')
            ->orderBy($order_by_fields);

        $counter = 0;

        foreach($columns as $columnDesc) {

            if($columnDesc['searchable'] === 'true') {

                $counter++;

                $builder->orWhere('P.'.$columnDesc['name'].' LIKE :'.$counter.':', array($counter => $search['value'] . '%'));

            }

        }

        $paginator = new QueryBuilder(array(
            "builder" => $builder,
            "limit" => $limit,
            "page" => $current_page
        ));

        $page = $paginator->getPaginate();
        $data = array(
                'draw' => $draw,
                'recordsTotal'    => $page->total_items,
                'recordsFiltered' => $page->total_items,
                'data' => $page->items->toArray(),
        );


1.7k
Accepted
answer
edited Sep '14

UPD2: Colleague helped me to figure this out, the bug was in the query phalcon produces: count() of the group by counts grouped elements. So a workaround looks like:

$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;

UPD: perhaps, a query should look like:

SELECT SQL_CALC_FOUND_ROWS * FROM ...;
SELECT FOUND_ROWS() AS `rowcount`;


60

The paginator do not work correct yet when is groupBy and join in sql query.