Mssql Sorting Problem

Yii 1.1 General Discussion
1

Hi all,

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?

Thanks in advance

2

Extend CGridView

3

already tried didn’t work

4

I tried overwriting the gridview and/or the Csort, but neither worked. It seems sorting with two columns doesn’t work.

Using MSSQL and Yii version: 1.1.8

Seems to be related to this bug:

http://code.google.com/p/yii/issues/detail?id=2756

Any ideas ?

5

I am also experiencing this problem. When I set a defaultOrder or sort by a column, I can go to the next page.

But if I try to sort by a relation’s column and switch pages, I get the SQL error “The multi-part identifier could “relation.column” not be bound”.

6

Hi

I am not sure this is exactly related, but it reminds me of a bug on the limits (which is still unresolved) which I reported about a year ago:

https://code.google…/detail?id=3211

It might be part of the solution.

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;

	}
7

Thanks, that fixed it

8

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;

		*/

	}