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 thats 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__]
I don’t understand why SQL query for every page is selecting TOP 20 results.
I tried searching about it but did not find any result. Hope someone of you can please help me out with this.
Item 1: It’s strange that page 1 works, because of the BETWEEN dates that you use. Most SQL dbs store date as Y-m-d (2014-01-31). But OK…
Item 2: As to your question: Just a thought what happens if you don’t set the totalCount? I haven’t used the CSqlDataProvider, so I may be totally off base. Also what is the actual value of $count after you set it? Check on the first page AND a couple of other pages.
I am using SQL Server 2012 and it gives me the same result for both Y-m-d and D-m-y format.
If I don’t set the totalItemCount pagination wont happen and in summary of grid view it will show ‘Displaying -19-0 of 0 results.’
Once I set the $count its actual value is 64 and as summary for 1st page it shows ‘Displaying 1-20 of 64 results.’ and for second ‘Displaying 21-40 of 64 results.’ and for last ‘Displaying 45-64 of 64 results.’.
So everything looks fine here.
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 everytime it is selecting TOP 20 rows. Maybe if anyone would have used sql server and pagination may provide an example query(from log messages on web page) it uses to retrieve data for pages 2,3… so that I can compare the difference and try to find out a solution.
I tried that but still does not make any change. It is still using the same query for calculating results for every page. Why does it keeps SELECTING TOP 20 rows for every page?? Its been three days now and I am not able to solve this problem
I was looking at some other postings and noticed that when using a ‘COUNT * FROM…’ sql statement, they used ->queryScalar() instead of query().
Just a thought. Also I was asking what the actual value returned from the ‘COUNT’ query was. If it was not what is expected then you need to look at that part of the code.
The issue is solved with the help of this Post I had to change rewriteLimitOffsetSql function in CMssqlCommandBuilder.php to use OFFSET & FETCH and then the pagination works totally fine.
