How to use limit sql with Paginator ?

Question

How to use limit sql with Paginator ?

kcloze Jul '16

Created Jul '16	Last Reply Jul '16	Replies 6	Views 1111	Votes 0

kcloze 1.7k

Jul '16

my code :

if ($this->request->isPost()) {
            $query                          = Criteria::fromInput($this->di, "Phalcon\Models\DvbAdminLogger", $this->request->getPost());
            $this->persistent->searchParams = $query->getParams();
        } else {
            $numberPage = $this->request->getQuery("page", "int");
        }

        $parameters = array();
        if ($this->persistent->searchParams) {
            $parameters = $this->persistent->searchParams;
        }

        $adminLogger = DvbAdminLogger::find($parameters);

        $paginator = new Paginator(
            array(
                "data"  => $adminLogger,
                "limit" => 15,
                "page"  => $currentPage,
            )
        );
        $page          = $paginator->getPaginate(); //$page->items为当前页数据
        $page->pageUrl = '' . $this->dispatcher->getActionName(); //分页点击的url

I find something terrible in Paginator, when I select one page, the data need select all from database. but if there is a big data from a table, how to use limit in Paginator?

Wojciech Ślawski · Answer 1 · 2016-07-08T03:20:15-07:00

Beacause you are selecting all data yourself:

$adminLogger = DvbAdminLogger::find($parameters);

I think you need to pass query builder to paginator.

Lajos Bencz · Answer 2 · 2016-07-08T03:42:53-07:00

As @jurigag said, you'll have to use the QueryBuilder adapter https://docs.phalcon.io/en/latest/reference/pagination.html

Your current implementation uses NativeArray, which first pulls all records then sifts through the PHP array.

Wojciech Ślawski · Answer 3 · 2016-07-08T04:07:18-07:00

It's using currently resultset, not NativeArray, but result is the same anyway.

Well, tbh it could be nice to provide maybe just Phalcon\Mvc\Model\Criteria, don't know why there is no currently such an option to provide just DvbAdminLogger::query()

kcloze · Answer 4 · 2016-07-08T04:14:59-07:00

kcloze
1.7k

Jul '16

this is a basic feature, but I can't find any example.

Wojciech Ślawski · Answer 5 · 2016-07-08T05:06:59-07:00

$builder = $this->modelsManager->createBuilder()
->from(DvbAdminLogger::class);

        $paginator = new Paginator(
            array(
                "data"  => $builder ,
                "limit" => 15,
                "page"  => $currentPage,
            )
        );

Nikolay Mihaylov · Answer 6 · 2016-07-08T07:20:22-07:00

As the guys above mentioned you should use the QueryBuilder for that task. Here are examples from the docs with all possible adapters: https://docs.phalcon.io/en/latest/reference/pagination.html

Please note this in the above link: Model - Use a Phalcon\Mvc\Model\Resultset object as source data. Since PDO doesn’t support scrollable cursors this adapter shouldn’t be used to paginate a large number of records

Another reference: https://forum.phalcon.io/discussion/1107/pagination-with-resultset

Random QueryBuilder example from old project of mine:

public function galleryAction()
{
    $builder = $this->modelsManager->createBuilder();
    $builder->columns([
        'reg.id', 
        'reg.names', 
        'reg.email', 
        'reg.phone', 
        'artist.filename as artistImage', 
        'model.filename as modelImage'
    ]);
    $builder->from(['reg' => 'Models\Registrations']);
    $builder->leftJoin('Models\Uploads', 'artist.foreign_key = reg.id AND artist.section = "registration-artist-image" AND artist.is_active = "1"', 'artist');
    $builder->leftJoin('Models\Uploads', 'model.foreign_key = reg.id AND model.section = "registration-model-image" AND model.is_active = "1"', 'model');
    $builder->where('reg.status = 1');
    $builder->orderBy('reg.created_at DESC'); 
    $paginator = new \Phalcon\Paginator\Adapter\QueryBuilder([
        'builder' => $builder,
        'limit'   => 15,
        'page'    => (int) $this->request->get('page')
    ]);
    $this->view->pager = $paginator->getPaginate();
    $this->view->items = $this->view->pager->items;
}

Lajos Bencz · Answer 7 · 2016-07-11T01:36:40-07:00

Pasting in the docs url for the second time (which has examples): https://docs.phalcon.io/en/latest/reference/pagination.html

this is a basic feature, but I can't find any example.