php - Using SQL_CALC_FOUND_ROWS within a SqlDataProvider in Yii2 -

i using sqldataprovider in yii2 , here general example:

$count = yii::$app->db->createcommand('     select count(*) user status=:status ', [':status' => 1])->queryscalar();  $dataprovider = new sqldataprovider([     'sql' => 'select * user 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 count in query before actual query within sqldataprovider gets actual data results.

however prefer use slc_calc_found_rows number more reliable method correct number matches actual amount of rows returned query inside dataprovider it's possible matching rows added or deleted between count query , sqldataprovider queries , hence need more reliable.

i lock tables, don't think that's such wise idea, need use sql_calc_found_rows correct amount unsure how can dataprovider.

this code want:

$sql = $this->db->createcommand("select found_rows()"); $count = $sql->queryscalar();  $dataprovider->totalcount = $count; 

...but doesn't work, said unsure how implement code work sqldataprovider.

from understanding, way sqldataprovider functions follows:

• if no pagination set, dataprovider query db , count() models generated result. this behavior want.
• if pagination set either use value provided $totalcount, or if $totalcount == null return value of sqldataprovider::preparetotalcount() set return 0. not behavior want.

i don't think possible both take advantage of pagination in queries , exact total count without 2 queries. after whole point of pagination not have handle returned elements.

i see 2 possibilities.

you either remove pagination , handle separately. viable if know return set going relatively small. in practical situations not option.

which leads having run 2 queries. if you're ok idea of 2 queries , feel necessary execute them close possible, here's how can proceed best results:

• extend sqldataprovider new class.. lets call customsqldataprovider
• set new public $totalcountcommand property.
• write preparetotalcount() method override default behavior

something along lines of:

protected function preparetotalcount() {     return $this->totalcountcommand->queryscalar(); } 

then can create dataprovider alongs lines of :

$countcommand = yii::$app->db->createcommand('     select count(*) user status=:status ', [':status' => 1]);  $dataprovider = new customsqldataprovider([     'sql' => 'select * user 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 should do, run count query when dataprovider gets result set opposed had count done when dataprovider set (well technically before set)

ps: have not tested code, read yii2 code. should, however, either work minor tweaks, or set on correct path. let me know if need info.