Using SQL_CALC_FOUND_ROWS within a SqlDataProvider in Yii2

Question

I am using a SqlDataProvider in Yii2 and here is the general example:

$count = Yii::$app->db->createCommand('
    SELECT COUNT(*) FROM user WHERE status=:status
', [':status' => 1])->queryScalar();

$dataProvider = new SqlDataProvider([
    'sql' => 'SELECT * FROM user WHERE status=:status',
    'params' => [':status' => 1],
    'totalCount' => $count,
    'sort' => [
        'attributes' => [
            'age',
            'name' => [
                'asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC],
                'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC],
                'default' => SORT_DESC,
                'label' => 'Name',
            ],
        ],
    ],
    'pagination' => [
        'pageSize' => 20,
    ],
]);

You can see this does a COUNT in a query before the actual query within the SqlDataProvider that gets the actual data results.

However I would prefer to use SLC_CALC_FOUND_ROWS as this number is a more reliable method to get the correct number that matches the actual amount of rows returned by the query inside the DataProvider as it's possible that matching rows could get added or deleted between the COUNT query and the SqlDataProvider queries and hence I need something more reliable.

I could lock the tables, but I don't think that's such as wise idea, so I need to use SQL_CALC_FOUND_ROWS to get the correct amount but I am unsure how I can do it with a dataProvider.

This would be the code to do what I want:

$sql = $this->db->createCommand("SELECT FOUND_ROWS()");
$count = $sql->queryScalar();

$dataProvider->totalCount = $count;

...but that doesn't work, so as I said I am unsure how to implement the code to work with a SqlDataProvider.

Answer 1

From my understanding, the way SqlDataProvider functions is as follows:

• If no pagination is set, the dataprovider will query the DB and then count() the models generated by the result. This is the behavior you want.
• If pagination is set it will either use the value provided by $totalCount, or if $totalCount == NULL will return the value of SqlDataProvider::prepareTotalCount() which is set to return 0. Not the behavior you want.

I don't think it is possible to both take advantage of pagination in your queries and get the exact total count without two queries. After all the whole point of pagination is to not have to handle all the returned elements.

I see two possibilities.

You either remove pagination and handle it separately. This is only really viable if you know your return set is going to be relatively small. In most practical situations this is not an option.

Which leads us to having to run two queries. If you're ok with the idea of two queries and you feel it is necessary to execute them as close together as possible, here's how you can proceed to get the best results:

• Extend SqlDataProvider into a new class.. lets call it CustomSqlDataProvider
• Set a new public $totalCountCommand property.
• Write a prepareTotalCount() method to override the default behavior

Something along the lines of:

protected function prepareTotalCount()
{
    return $this->totalCountCommand->queryScalar();
}

Then you can simply create your dataprovider alongs the lines of :

$countCommand = Yii::$app->db->createCommand('
    SELECT COUNT(*) FROM user WHERE status=:status
', [':status' => 1]);

$dataProvider = new CustomSqlDataProvider([
    'sql' => 'SELECT * FROM user WHERE status=:status',
    'params' => [':status' => 1],
    'totalCountCommand' => $countCommand,
    'sort' => [
        'attributes' => [
            'age',
            'name' => [
                'asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC],
                'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC],
                'default' => SORT_DESC,
                'label' => 'Name',
            ],
        ],
    ],
    'pagination' => [
        'pageSize' => 20,
    ],
]);

What this should do, is run your count query when the dataprovider gets the result set as opposed to what you initially had where the count was done when the dataprovider was set (well technically even before it was set)

PS: I have not tested this code, just read the yii2 code. It should, however, either work with minor tweaks, or set you on the correct path. Let me know if you need any extra info.

Answer 2

UPDATE: I have modified my answer to allow page validation.

---

I arrive over one year late, but I wanted to share that it is actually possible to use SQL_CALC_FOUND_ROWS within a SqlDataProvider in Yii2.

You need to extend the SqlDataProvider class to:

• get the total count from SELECT FOUND_ROWS().
• modify how the validatePage property of the pagination object works.

What is the problem?

If you have a look to the last lines of the prepareModels() method of the SqlDataProvider class (Yii 2.0.10)...

if ($pagination !== false) {
    $pagination->totalCount = $this->getTotalCount();
    $limit = $pagination->getLimit();
    $offset = $pagination->getOffset();
}

$sql = $this->db->getQueryBuilder()->buildOrderByAndLimit($sql, $orders, $limit, $offset);

return $this->db->createCommand($sql, $this->params)->queryAll();

...you will see that $this->getTotalCount() is called before running the query. Obviously, that is a problem if you want to use SELECT FOUND_ROWS() for the total count.

But, why does it need to call it in advance? After all, at that point it hasn't started to build the pager yet. Well, the pagination object needs the total count just for validating the current page index.

The getOffset() method calls getPage() for the calculation, which calls getQueryParam() to obtain the current requested page. After doing so, getPage() calls setPage($page, true). And here is when the total count is necessary: setPage() will call getPageCount() to ensure that the requested page is within the boundaries.

What is the solution?

To extend the SqlDataProvider class, setting the validatePage property of the pagination object to false until we have executed our query. Then we can get the total count from SELECT FOUND_ROWS() and enable a custom page validation.

Our new custom data provider could be something like this:

use Yii;
use yii\data\SqlDataProvider;

class CustomDataProvider extends SqlDataProvider
{
    protected function prepareModels()
    {
        // we set the validatePage property to false temporarily 
        $pagination = $this->getPagination();
        $validatePage = $pagination->validatePage;
        $pagination->validatePage = false;

        // call parent method 
        $dataModels = parent::prepareModels();

        // get total count
        $count = Yii::$app->db->createCommand( 'SELECT FOUND_ROWS()' )->queryScalar();

        // both the data provider and the pagination object need to know the total count 
        $this->setTotalCount( $count );
        $pagination->totalCount = $count;

        // custom page validation       
        $pagination->validatePage = $validatePage;
        // getPage(true) returns a validated page index if $validatePage is also true
        if ( $pagination->getPage(false) != $pagination->getPage(true) ) { 
            return $this->prepareModels();
            // or if you favor performance over precision (and fear recursion) *maybe* is better:
            //$this->sql = str_replace( 'SQL_CALC_FOUND_ROWS ', '', $this->sql);
            //return parent::prepareModels();
        }

        return $dataModels;
    }
}

And we can use it in this way:

$dataProvider = new CustomDataProvider([
    'sql' => 'SELECT SQL_CALC_FOUND_ROWS ...';
    //'totalCount' => $count, // this is not necessary any longer!
    // more properties like 'pagination', 'sort', 'params', etc.
]);

Is there any drawback?

Well, our new custom page validation is less efficient: if the page doesn't pass the validation it will need one extra query.

How does the page validation work?

Imaging that you have 100 items and you use a data provider with pageSize => 20 to display data in a ListView. The pager will show links to navigate through 5 pages but, in some cases, the user can try to access page 6: because he manually modifies the URL, because the number of records changes since he last loaded the page (like in @Brett's example) or because he follows an old link.

How does the data provider manage the situation? If validatePage is set to...

• false (whatever provider): it will try to query for the page 6 with a SQL like: SELECT ... LIMIT 20 OFFSET 100. It will get an empty dataset and the widget will output 'No results found.'
• true (SqlDataProvider): it will detect beforehand that the last available page is the number 5 and it will query for it with SELECT ... LIMIT 20 OFFSET 80.
• true (CustomDataProvider): it will try to query for the page 6, get an empty dataset, realize afterwards that page 6 doesn't exist and query again for page 5.

IMO, it is not a big deal because getting into a non-existing page will happen very rarely.

Is this actually necessary?

The OP wanted this approach to ensure that both the counting and the actual query are executed as close as possible. Maybe you want it for performance.

In any case, you should read the comments to the question from @AlexanderRavikovich and @ineersa. It is counterintuitive, but in a lot of cases a second count(*) query can be faster than using SQL_CALC_FOUND_ROWS.

There is a lot written about it, don't bother too much: it depends a lot on your query and the database version. The best you can do is to test both ways before implementing a custom data provider.

Final notes:

If you really care about precision, consider this scenario:

• If count(*) fails, it will normally fail for a few records.
• If SELECT FOUND_ROWS() fails... well, it can be an epic fail!

And if you really care about performance, there are some nice suggestions in the answers to this other question (take it with a grain of salt, it is very old), I like this one specially.

Answer 3

Create your query like this :

$queryResults  = Yii::app()->db->createCommand()
    ->select('SQL_CALC_FOUND_ROWS (0), ' .
             'table1.column_1, table1.column_n')
    ->from('table1')
    ->where('status=1')
    ->queryAll();

$totalRecords =  Yii::app()->db
    ->createCommand('SELECT FOUND_ROWS()')
    ->queryScalar();
$totalFetched =  count($queryResults);

echo 'Fetched '.$totalFetched.' of '.$totalRecords.' records.';

Answer 4

You need 2 queries in order to get total rows if you want to use SQL_CALC_FOUND_ROWS instead of COUNT. Here SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE status = 1 LIMIT 10; will return to you result of the query which is limited by 10 rows, but because you are using SQL_CALC_FOUND_ROWS it will count total number of items which fit query conditins and remember it. After that you use SELECT FOUND_ROWS(); to get this number from dbms.

ADDED

Code that i wrote to test it on one of my projects:

Yii::$app->db->createCommand('SELECT SQL_CALC_FOUND_ROWS * FROM {{%articles}} LIMIT 1')->queryScalar();
$count = Yii::$app->db->createCommand('SELECT FOUND_ROWS()')->queryScalar();
$dataProvider = new \yii\data\SqlDataProvider([
    'sql' => 'SELECT * FROM {{%articles}}',
    'totalCount' => $count,
    'pagination' => [
        'pageSize' => 2,
    ],
]);
echo $count . ' ' . count($dataProvider->getModels());

It output me 5 2 where 5 is the total number of items and 2 is number of items fetched for page