CPagination with MSSQL

Yii 1.1 General Discussion
1

Has anyone had the following issues with CPagination using MSSQL.

Scenario

Page 1 Yii generated query


 SELECT TOP 10 * FROM [dbo].[widgets] [t] WHERE Active

= 'Y' AND (ExpiryDate > Convert(datetime, Convert(int, GetDate())) OR

ExpiryDate IS NULL)

Page 2 Yii generated query


SELECT * FROM (SELECT TOP 10 * FROM (SELECT DISTINCT TOP 20 *

FROM [dbo].[widgets] [t] WHERE Active = 'Y' AND (ExpiryDate >

Convert(datetime, Convert(int, GetDate())) OR ExpiryDate IS NULL) ORDER BY

ID) as [__inner__] ORDER BY ID DESC) as [__outer__] ORDER BY ID ASC

It seems that the ROW_NUMBER() method used in MSSQL which is equivalent to the Limit, Offset approach used in MySQL is not being applied by CPagination.

Any ideas?

2

http://www.yiiframework.com/forum/index.php?/topic/23538-solved-cgridview-last-page-pagination-mssql/

3

Hi Rohit,

Thank you for the link but I managed to work on a solution last night.

I used the new ROW_NUMBER() and Common Table Expression method which works similar to MySQL LIMIT total,offset method. Makes pagination queries in MSSQL a little more elegant to work with.

The solution is all contained within the rewriteLimitOffsetSql($sql, $limit, $offset) method found in the CMsCommandBuilder class file.

Here is my code:





$fetch = $limit+$offset;

$newOffSet = $offset+1;

		

/**

* Extract SELECT statements from the $sql variable.

*/		 

preg_match_all('/(SELECT)[\s"\[](.*?)(FROM)/i', $sql, $s);

$select = $s[2][0];


/**

 * Extract DISTINCT statements from the $sql variable.

 */		 

preg_match_all('/(SELECT)[\s"\[](.*?)(TOP)/i', $sql, $d);

$distinct = $d[2][0];


/**

 * Extract COLUMN names from the $sql variable

 */

preg_match_all('/('.$limit.')[\s"\[](.*?)(FROM)/i', $sql, $c);

$columns = $c[2][0];

$first_column = explode(" AS ",$columns); 

$orderbycol = $first_column[0];//needed for ordering


/**

 * Extract TABLE name from the $sql variable

 */

preg_match_all('/(FROM)[\s"\[](.*?)(WHERE|ORDER|$)/i', $sql, $t);

$table = $t[2][0];	


/**

 * Extract WHERE conditions from the $sql variable 

 */		 

preg_match_all('/(WHERE)[\s"\[](.*?)(ORDER|$)/i', $sql, $w);		

$where = $w[1][0]. ' '.$w[2][0];				




$ordering = $this->findOrdering($sql);

$orginalOrdering = ($ordering!=NULL)?$this->joinOrdering($ordering, '[__outer__]'):' ORDER BY '.$orderbycol;

$reverseOrdering = $this->joinOrdering($this->reverseDirection($ordering), '[__inner__]');

		

$sql = "WITH pagination AS

		  (					  

			  SELECT {$distinct}

				  ROW_NUMBER() OVER ({$orginalOrdering}) AS rowNo,

				  {$columns}

			  FROM

				 {$table} {$where}

		  )

		  SELECT *, (select count(*) FROM pagination) AS TotalRows

		  FROM

			  pagination

		  WHERE 

			  RowNo BETWEEN $newOffSet AND $fetch     

		  ORDER BY

			  rowNo";				

		

return $sql;

@Note This solution my need some tweaking for your application and will only work with MSSQL 2005 and above.

4

Just wanted to share my idea as well for sqlserver2005 and above. BTW, this is only tested for a few queries, so may not be perfect all the way. Your query gave me error on my queries…




$fetch = $limit+$offset;

$offset = $offset+1;

$order_Pattern = '/(.*)ORDER[\s]?BY(.*)/i';

$ordering       = $this->findOrdering($sql);

$order = $this->joinOrdering($ordering,'');

$newSql = preg_replace($order_Pattern,"$1", $sql);                

$select_Pattern = '/^(.*)SELECT(.*)/i';                

$newQuery = preg_replace($select_Pattern,"SELECT ROW_NUMBER () OVER ($order) as rank, * FROM ($1 SELECT $2) AS TEMP",$newSql);

$finalQuery = "SELECT * FROM ($newQuery) as final WHERE rank between $offset and $fetch";

return $finalQuery;
5

Your snippet is alot cleaner and have implemented it into my application. Seems to work in most cases but have now encounted a problem with the ORDER BY clause dropping off the table alias.




// This is returning .[order_column] instead of [t].[order_column]

$order = $this->joinOrdering($ordering,'');

have you encounted this issue?

6

Yes, I did. I had to add another line on joinOrdering

Btw, also one more thing you will have to be careful about is on the CSort(). You will have to set rules with ‘[]’ ( fun part of dealing with sqlserver)




$sort->defaultOrder = '[t].[date] desc';

Here is my complete code of joinOrdering method




	protected function joinOrdering($orders, $newPrefix)

	{

		if(count($orders)>0)

		{

			$str=array();

			foreach($orders as $column => $direction)

				$str[] = $column.' '.$direction;

			$orderBy = 'ORDER BY '.implode(', ', $str);

                        

                        if ($newPrefix != "" ){

                            $result = preg_replace('/\s+\[[^\]]+\]\.(\[[^\]]+\])/i', ' '.$newPrefix.'.$1', $orderBy);

                        }

                        else $result = preg_replace('/\s+\[[^\]]+\]\.(\[[^\]]+\])/i', ' '.$newPrefix.'$1', $orderBy);

                        

                        

                        return $result;

		}

	}
7

Nice… I also had to implement the fix.

Other developers are having similar issues which Qiang knows about and might make into the next release.

By Qiang - http://code.google.com/p/yii/issues/detail?id=2923

MSSQL has been quite painful with Yii, especially with pagination, sorting and TEXT/VARCHAR(MAX) column types but now that my project is near it’s end I’m quite happy how its all paned out.

8

Thanks a lot for this solution…!!!

I’ve finally implemented my grid with CSqlDataProvider and correct pagination + sorting on Sql Server 2005…!

Notice that ‘defaultOrder’ is required for paginating the results.




$count = Yii::app()->db->createCommand('SELECT COUNT(*) FROM StLog')->queryScalar();

        $sql = 'SELECT StID, StName, StAutor, StMag, YEAR(StDate) as StDate FROM StLog';

 

        $dataProvider = new CSqlDataProvider($sql, array(

            'totalItemCount'=> $count,

            'sort'=>array(

                'attributes'=>array(

                    'StID',

                    'StName',

                    'StAutor',

                    'StDate',

                    'StMag',

                ),

               'defaultOrder' => 'StID',  // REQUIRED!!!

            ),

            'pagination' => array(

                'pageSize'=>4,

            ),

            'keyField' => 'StID'

        ));
9

Hi

For anyone reading this, you might be interested in the solution I proposed in my bug report:

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

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

	}