CODEWITHSUNDEEP
phpgridviewpaginationyii2
Sam Ivichuk
Sam Ivichuk

Reputation: 1038

Yii2 GridView pagination with only next and prev links and no TotalCount

There is a huge DB table with millions of rows and it needs to be outputed in a GridView with only prev and next links in the paginator.

I don't want to use 'select count(*)' on such tables, so there's no TotalCount. Also I want to prevent users from setting huge offset and degrade MySQL performance.

Can anyone help me?

Upvotes: 3

Views: 2760

Answers (2)

Sam Ivichuk
Sam Ivichuk

Reputation: 1038

I've waited several days to make sure I wasn't missing some obvious solution, but now need to hardcode it on my own. The quickest way I've found is to extend DataProvider and rewrite methods: prepareTotalCount(), prepareModels():

namespace common;
use yii\data\ActiveDataProvider;
use yii\base\InvalidConfigException;
use yii\db\QueryInterface;

class BigActiveDataProvider extends ActiveDataProvider
{
    protected function prepareTotalCount() {
        return 0;
    }

    protected function prepareModels()
    {
        if (!$this->query instanceof QueryInterface) {
            throw new InvalidConfigException('The "query" property must be an instance of a class that implements the QueryInterface e.g. yii\db\Query or its subclasses.');
        }
        $query = clone $this->query;

        if (($pagination = $this->getPagination()) !== false) {
            $pagination->validatePage = false;
            $page = $pagination->getPage(true) + 1;
            $offset = $page*$pagination->getPageSize();

            $query->limit($pagination->getLimit() + 1)->offset($offset);
        }
        if (($sort = $this->getSort()) !== false) {
            $query->addOrderBy($sort->getOrders());
        }

        $res = $query->all($this->db);

        if (($pagination = $this->getPagination()) !== false) {
            $pagination->totalCount = ($page)*$pagination->getPageSize();
            if (count($res) > $pagination->getPageSize()) {
                unset($res[count($res)-1]);
                $pagination->totalCount++;
            }
        }

        return $res;
    }
}

I don't think that this is the best solution, but it works as planned and it doesn't limit ActiveDataProvider capabilities. It only makes sure that count(*) query won't be executed and there is no need to set totalCount.

Upvotes: 0

Maddelynne P
Maddelynne P

Reputation: 624

When you create your dataprovider, make sure to specify the totalCount to a number, such as 0, which will prevent Yii from running a count(*) query.

You should then create a replacement class for yii\widgets\Linkpager that generates only the links you want to appear.

Finally, attach your pager to your GridView.

<?php GridView::widget([
'dataProvider'=>$dataProvider,
'pager' => [
   'class' => 'path\to\my\custom\Pager\'
 ]
 'columns' => 
         ....
]; ?>

Upvotes: 1

Related Questions