I am having problems with sorting gridviews and than using the pager.
If I sort a column and than use the pager, than switch to page 1, 2, 3 etc…
Sometimes the pages are identical, it can go on for like 8 pages or more.
Found a solution for it by adding an additional table column (default order) to sort in the dataprovider->sort->attributes
'name' =>
array(
'asc' => 'name ASC, time DESC',
'desc' => 'name DESC, time DESC',
),
But my problem is that i have like 100 gridviews with the same problem. Is there a way to simply overwrite CSort or something that applies this to all dataproviders?
My proposal is to update the CPaginator code with:
/**
* Applies LIMIT and OFFSET to the specified query criteria.
* @param CDbCriteria $criteria the query criteria that should be applied with the limit
*/
public function applyLimit($criteria) {
$limit=$this->getLimit();
$offset=$this->getOffset();
$itemcount=$this->getItemCount();
if($itemcount!=0 && ($limit+$offset)>$itemcount) {
$limit=max(0,$itemcount-$offset);
}
$criteria->limit=$limit;
$criteria->offset=$offset;
}
My previous problem was a simple enough to solve by setting the ‘with’ property and by applying a default sort column.
But now I am experiencing the OP’s problem even after applying le_top’s CPagination change.
My table has 11666 records and pages 18 and 19 have identical results.
I took a look at the queries that were generated and they indeed do give the same results when executed in SQL Server Management Studio.
The inner most subquery is fine and delivers different results for both "pages". However, when the outer subquery is applied the same results are collected. MSSQL now supports OFFSET and FETCH so I might look into translating from the MySQL queries.
Edit: I just fixed my problem. In CMssqlCommandBuilder there is a method called rewriteLimitOffsetSql (this method attempts to rewrite the limit portion of queries using the "subquery method"). As I mentioned before since MSSQL now supports OFFSET and FETCH I just commented out the code and replaced it with a single line that translates to the appropriate syntax usage.
protected function rewriteLimitOffsetSql($sql, $limit, $offset)
{
return $sql." OFFSET ".$offset." ROWS FETCH NEXT ".$limit." ROWS ONLY";
/*
$fetch = $limit+$offset;
$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $fetch", $sql);
$ordering = $this->findOrdering($sql);
$orginalOrdering = $this->joinOrdering($ordering, '[__outer__]');
$reverseOrdering = $this->joinOrdering($this->reverseDirection($ordering), '[__inner__]');
$sql = "SELECT * FROM (SELECT TOP {$limit} * FROM ($sql) as [__inner__] {$reverseOrdering}) as [__outer__] {$orginalOrdering}";
return $sql;
*/
}