Brett Brett - 21 days ago 7
MySQL Question

Using SQL_CALC_FOUND_ROWS within a SqlDataProvider in Yii2

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

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.