phalcon querybuilder total_items always returns 1

Question

phalcon querybuilder total_items always returns 1

ongley Aug '14

Created Aug '14	Last Reply Sep '15	Replies 2	Views 946	Votes 0

ongley 1.7k

Aug '14

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

ongley
1.7k

Accepted
answer

edited Sep '14
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`;

Phalcon · Answer 1 · 2014-08-26T11:24:02-07:00

Phalcon
98.9k

Aug '14

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

ongley · Answer 2 · 2014-08-26T23:31:17-07:00

        $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(),
        );

waldiM · Answer 3 · 2015-09-28T05:51:42-07:00

waldiM
60

Sep '15

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