Yii Pagination Result using CSqlDataProvider

Question

I am using pagination with CSqlDataProvider. Result is shown properly for the first 20 or first page of the pagination. When I see the Log message on the web page the query it uses is actually selecting top 20 rows for all the pages and that's the problem. I have no idea how to correct it.

My controller has code:

public function actionLink()
 {
      $sql = "SELECT Ordernumber, Order_Date
                FROM [Orders]
                WHERE CAST(Order_Date As Date) BETWEEN '01-01-2014' AND '31-01-2014'";

      $count = Yii::app()->db->createCommand('SELECT COUNT(*) FROM (' . $sql . ') as count_alias')->queryScalar();
      $dataProvider = new CSqlDataProvider($sql, array('keyField' => 'Order_Date',
           'totalItemCount' => $count,
           'pagination' => array(
           'pageSize' => 20,),));

      $this->render('link',  array('dataProvider' => $dataProvider));
    }}

And now the query which it uses for count is correct(Log Message):

Querying SQL:

SELECT COUNT(*) FROM (SELECT Ordernumber ,Order_Date
FROM [Orders]
WHERE CAST(Order_Date As Date) BETWEEN '01-01-2014' AND '31-01-2014') as count_alias

The query it uses for page 2 is(Log Message):

 SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 40 Ordernumber,Order_Date
 FROM [Orders]
 WHERE CAST(Order_Date As Date) 
 BETWEEN '01-01-2014' AND '31-01-2014') as [__inner__]) as [__outer__]

The above query actually will give result as first 20 rows. Its the same problem with every page. It gives every result as the same first 20 rows. So Page 3 becomes(Log Message):

 SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 60 Ordernumber,Order_Date
 FROM [Orders]
 WHERE CAST(Order_Date As Date) 
 BETWEEN '01-01-2014' AND '31-01-2014') 
 as [__inner__] ) as [__outer__]

The only problem I can see is the query it is using to retrieve the data for page 2,3.. and so on. For every page it returns the same data because every time it is selecting TOP 20 rows. I don't understand reason for this behavior. How does the query actually looks like when you use SQL SERVER as DB and CSqlDataProvider as data provider during pagination?

Maybe Yii is having trouble making SQLServer do pagination like mysql does. Look at this link: http://stackoverflow.com/a/10639172/436721 In my opinion, the issue would be that, since not many people use SQLServer with PHP (and Yii), this slight mistake may have been going on for these years. — Felipe, May 01 '14 at 23:40
@FelipeAlmeida I hope thats not the case. I will have to look for someone who used SQLServer as DB and did pagination and ask for his queries used to retrieve data for different pages — neophyte, May 02 '14 at 13:03

score 2 · Accepted Answer · answered May 02 '14 at 14:25

I found the solution. I had to change the rewriteLimitOffsetSql function in CMssqlCommandBuilder to:

return $sql." OFFSET ".$offset." ROWS FETCH NEXT ".$limit." ROWS ONLY";

Just this one line in function and remove the rest of the code. Good that SQL SERVER started support for OFFSET and FETCH.

Yii Pagination Result using CSqlDataProvider

1 Answers1

Linked