Yii Framework Forum: Mssql Sorting Problem - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Mssql Sorting Problem Rate Topic: -----

#1 User is offline   Balazs Gerendai 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 30-November 12

Posted 04 February 2013 - 07:38 AM

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
0

#2 User is offline   PeRoChAk 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 578
  • Joined: 26-November 10
  • Location:Lahore, Pakistan

Posted 04 February 2013 - 07:56 AM

Extend CGridView
Cheers
Perochak
Web Design - Logo Design - Application Development

My Blog | Forums Comparison
0

#3 User is offline   Balazs Gerendai 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 30-November 12

Posted 04 February 2013 - 08:02 AM

View PostPeRoChAk, on 04 February 2013 - 07:56 AM, said:

Extend CGridView

already tried didn't work
0

#4 User is offline   Balazs Gerendai 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 30-November 12

Posted 04 February 2013 - 09:24 AM

View PostBalazs Gerendai, on 04 February 2013 - 07:38 AM, said:

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


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.c.../detail?id=2756

Any ideas ?
0

#5 User is offline   genosha 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 13-August 12

Posted 19 February 2013 - 07:48 PM

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".
0

#6 User is offline   le_top 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 315
  • Joined: 08-June 10
  • Location:France

Posted 19 February 2013 - 08:48 PM

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;
	}

0

#7 User is offline   Balazs Gerendai 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 30-November 12

Posted 21 February 2013 - 03:52 AM

View Postle_top, on 19 February 2013 - 08:48 PM, said:

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;
	}


Thanks, that fixed it
0

#8 User is offline   genosha 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 13-August 12

Posted 22 February 2013 - 10:02 PM

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;
		*/
	}

This post has been edited by genosha: 23 February 2013 - 06:13 AM

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users