CPagination with MSSQL

Has anyone had the following issues with CPagination using MSSQL.


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


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?

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,



				 {$table} {$where}


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




			  RowNo BETWEEN $newOffSet AND $fetch     




return $sql;

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

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;

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?

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)





			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;



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 -

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.

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,









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


            'pagination' => array(



            'keyField' => 'StID'



For anyone reading this, you might be interested in the solution I proposed in my bug report:…/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)	{




    	if($itemcount!=0 && ($limit+$offset)>$itemcount) {