I am using a SqlDataProvider and would like to be able to retrieve the totalCount property via other methods such as a callBack function or other…
For example, here is the example of how to use SqlDataProvider from the Yii2 docs:
$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.
Whilst this may seem like an unlikely scenario, it would be an issue on high volume websites with a lot of insert activity.
I’m not sure the best way to achieve this, but perhaps either by expanding the totalCount to allow callbacks to get the total count or by setting an option that you want Yii to automatically generate this value.
In addition to this, the same count should be made available to the totalCount property in the pagination instance.