Paginator and query builder

Need Help
1

Can Paginator be used with query builder?

If yes, anybody kind enough to share a working example?

I`m talking about something like (pseudocode):

$results = $connection
    ->newQuery()
    ->select('*')
    ->from('articles')
    ->where(['created >' => new DateTime('1 day ago'), ['created' => 'datetime']])
    ->order(['title' => 'DESC'])
    ->execute()
    ->fetchAll('assoc');

$pag = $this->paginate($results);

$this->set(compact('pag'));

Thank you.

2

I have just found it easier to us a different paginator. I use this:

But I modified slightly so it works with cake, yii2, and laravel as well. Though laravel has a pretty good paginator out of the box.

Here is an example usage, but have to convert to modern cake request, I was quick testing here:

    public function indexAdmin() {
        if ($this->chklog() == 'notlogged') {
            return $this->redirect('/');
        }
        $connection = ConnectionManager::get('default');
        $dogsearch = (isset($_REQUEST['psch']) <> '' ? $_REQUEST['psch'] : "");
        $aval = (isset($_REQUEST['aval']) <> '' ? $_REQUEST['aval'] : "");
        $this->request->session()->write('dogsearch', $dogsearch);
        $this->request->session()->write('dogaval', $aval);
        //$result = $connection->execute('SELECT COUNT(dogid) as total FROM dogs WHERE adopted = 0')->fetchAll('assoc');
        //$dogrows = $this->Dog->dogCountadmin($dogsearch, $aval);
        $dogsch = $dogsearch . "%";
        //$total = $articles->find()->where(['is_active' => true])->count();
        if ($aval == "n") {
            $result = $connection->execute('SELECT COUNT(dogid) as total FROM dc_dogs WHERE dogname LIKE :search and adopted = 1', array('search' => $dogsch))->fetchAll('assoc');
        }
        if ($aval == "y") {
            $result = $connection->execute('SELECT COUNT(dogid) as total FROM dc_dogs WHERE dogname LIKE :search and adopted = 0', array('search' => $dogsch))->fetchAll('assoc');
        }
        if ($aval == "") {
            $result = $connection->execute('SELECT COUNT(dogid) as total FROM dc_dogs WHERE dogname LIKE :search', array('search' => $dogsch))->fetchAll('assoc');
        }
        $dogrows = $result[0]['total'];
        //$result = $this->db->select('SELECT COUNT(dogid) as total FROM ' . PREFIX . 'dc_dogs WHERE dogname LIKE :search',
        //array('search' => $dogsearch));

        $this->request->session()->write('dogrows', $dogrows);
        $pages = new HelpersPaginator('5', 'p');

        $this->request->session()->write('dogpage', $pages->getInstance());

        $pages->setTotal($dogrows);

        $pageLinks = $pages->pageLinks('?', '&psch=' . $dogsearch . '&aval=' . $aval);
        $offset = $pages->getLimit2();

        $rowsperpage = $pages->getPerpage();
        $pg = ($offset / $rowsperpage) + 1;
        $pagingQuery = "LIMIT {$offset}, {$rowsperpage}";
        $dogs = TableRegistry::get('Dogs');
        if ($aval == "y") {
            $query = $dogs->find()
                    ->where(['dogname LIKE' => $dogsch])
                    ->Where(['adopted' => 0])
                    ->orderDesc('lastedit')
                    ->limit($rowsperpage)
                    ->page($pg);
        }
        if ($aval == "n") {
            $query = $dogs->find()
                    ->where(['dogname LIKE' => $dogsch])
                    ->Where(['adopted' => 1])
                    ->orderDesc('lastedit')
                    ->limit($rowsperpage)
                    ->page($pg);
        }

        if ($aval == "") {
            //$query = $connection->execute('SELECT * FROM dc_dogs')->fetchAll('obj');
            $query = $dogs->find()
                    ->where(['dogname LIKE' => $dogsch])
                    ->orderDesc('lastedit')
                    ->limit($rowsperpage)
                    ->page($pg);
        }


        $this->viewBuilder()->layout('admintpl');
        $this->set(compact('query', 'pageLinks'));
        $this->render('/Dogs/indexadmin');
    }

And in view

echo '<td>' . $pageLinks . '</td>';

Querystring taken care of in pagelinks:

$pageLinks = $pages->pageLinks('?', '&psch=' . $dogsearch . '&aval=' . $aval);

Not trying to be bad here, but I got tired of fiddling with cakes paginator, the one I use and laravels in 1000% better and easier to work with.

1 Like
3

you can just give not executed (no ->toArray(), ->toList() ->first() ->all()) Query as argument

i.e.

$query = $this->Users->find()->where(['status' => 'active']);

$this->paginate($query);
4

Sadly, find() return and query() return behave differently in paginator, hence my question about query

Thanks for the alternative plugin tip, I’ll look into that