[Rohit Duhan]

If there are 21 items after pagination with pageSize of 20, CGridView displays 20 on the first page but then on the next page it displays 20 as well by including 21st item and grabbing 19 from the previous page.

I tried looking for a property that can disable this behavior but with no luck. Its probably a really easy fix, but can't seem to find after countless searches.
TIA

[Rohit Duhan]

I found the problem, and it will really help me if one of the Yii guru's can point me in the right direction.

I am implementing Yii on Sql Server 2008 and, here is the scenario:
I have got 63 total results, with a pageSize in Cpagination of 30. Therefore, I should have 3 results on the last page but I end up having 30.
Problem is this funciton MssqlCommandBuilder::rewriteLimitOffsetSql($sql, $limit, $offset)
which creates this query with $limit = 30, $offset = 30


SELECT * FROM (
SELECT TOP 30 * FROM
( SELECT TOP 90 [t].[Echo_Login_Id] AS [t0_c0], ..................


I was thinking about passing max no.s of rows of selection criteria, but then I am sure there is a better approach to this problem. Anyone ?

[Rohit Duhan]

I fixed it by adding one more property in the CdbCriteria for totalItemCount. Then, I passed that value in the mssqlSchema and calculated rows based on that. May be there is a better approach to this, but I think this is a bug in YII with mssql...

[wawancell]

Rohit Duhan, on 03 October 2011 - 04:38 PM, said:

I fixed it by adding one more property in the CdbCriteria for totalItemCount. Then, I passed that value in the mssqlSchema and calculated rows based on that. May be there is a better approach to this, but I think this is a bug in YII with mssql...

please share your code here

[vario]

I've encountered exactly the same problem with MS SQL & Yii, CPagination - the last page of the results is repeating the vast majority of the second to last page.

I'm unsure, as of yet, how to resolve it.

[Rohit Duhan]

I am adding my steps here, We are not supposed to change the core files but I didn't see any solution to it otherwise... If you can come up with something better, please share.


First, in CDbCriteria add a property that will store the total Item count
public $totalItemCount;

Then, in CActiveDataProvider fetchData() method, add this after $criteria=clone $this->getCriteria();
$criteria->totalItemCount = $this->getTotalItemCount();


In CActiveFinder createCommand($builder) method, change this line $sql=$builder->applyLimit($sql,$this->limit,$this->offset);
To
$sql=$builder->applyLimit($sql,$this->limit,$this->offset,$this->totalItemCount);


Finally, in CMssqlCommandBuilder replace applyLimit and rewriteLimitOffsetSql methods with this

	
public function applyLimit($sql, $limit, $offset, $totalItemCount = null )
	{   
		$limit = $limit!==null ? intval($limit) : -1;
		$offset = $offset!==null ? intval($offset) : -1;
		if ($limit > 0 && $offset <= 0) //just limit
			$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql);
		else if($limit > 0 && $offset > 0)
			$sql = $this->rewriteLimitOffsetSql($sql, $limit,$offset, $totalItemCount);
                
		return $sql;
	}

	protected function rewriteLimitOffsetSql($sql, $limit, $offset, $totalItemCount = null )
	{   
                $fetch = $limit+$offset;
                if ( $totalItemCount !== null && $limit + $offset > $totalItemCount )
                {                       
                    $limit = $totalItemCount - $offset;
                    $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;
	}

[DrBobbyDylan]

I have an alternative solution that doesn't require modification of framework files (version 1.1.10.r3566)

Simply extend CActiveDataProvider and update your models to use this as the data provider

class ActiveDataProvider extends CActiveDataProvider
{
	/**
	 * Fetches the data from the persistent data storage.
	 * @return array list of data items
	 */
	protected function fetchData()
	{
		$criteria=clone $this->getCriteria();

		if(($pagination=$this->getPagination())!==false)
		{
			$pagination->setItemCount($this->getTotalItemCount());
			$pagination->applyLimit($criteria);
		}

		// update limit to the correct value for the last page 
		$limit=$pagination->getLimit();
		$offset=$pagination->getOffset();
		if ( $offset+$limit > $pagination->getItemCount() )
			$criteria->limit = $pagination->getItemCount() - $offset;
		
		$baseCriteria=$this->model->getDbCriteria(false);

		if(($sort=$this->getSort())!==false)
		{
			// set model criteria so that CSort can use its table alias setting
			if($baseCriteria!==null)
			{
				$c=clone $baseCriteria;
				$c->mergeWith($criteria);
				$this->model->setDbCriteria($c);
			}
			else
				$this->model->setDbCriteria($criteria);
			$sort->applyOrder($criteria);
		}

		$this->model->setDbCriteria($baseCriteria!==null ? clone $baseCriteria : null);
		$data=$this->model->findAll($criteria);
		$this->model->setDbCriteria($baseCriteria);  // restore original criteria
		return $data;
	}

}

Then update your models eg

public function search()
	{
		$criteria=new CDbCriteria;
		return new ActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}

Same solution for CSqlDataProvider.

class SqlDataProvider extends CSqlDataProvider
{

	/**
	 * Fetches the data from the persistent data storage.
	 * @return array list of data items
	 */
	protected function fetchData()
	{
		$sql=$this->sql;
		$db=$this->db===null ? Yii::app()->db : $this->db;
		$db->active=true;
	
		if(($sort=$this->getSort())!==false)
		{
			$order=$sort->getOrderBy();
			if(!empty($order))
			{
				if(preg_match('/\s+order\s+by\s+[\w\s,]+$/i',$sql))
					$sql.=', '.$order;
				else
					$sql.=' ORDER BY '.$order;
			}
		}
	
		if(($pagination=$this->getPagination())!==false)
		{
			$pagination->setItemCount($this->getTotalItemCount());
			$limit=$pagination->getLimit();
			$offset=$pagination->getOffset();
			
            // update limit to the correct value for the last page 
			if ( $offset+$limit > $pagination->getItemCount() )
				$limit = $pagination->getItemCount() - $offset;
			
			$sql=$db->getCommandBuilder()->applyLimit($sql,$limit,$offset);
		}
	
		$command=$db->createCommand($sql);
		foreach($this->params as $name=>$value)
			$command->bindValue($name,$value);
	
		return $command->queryAll();
	}
}

When you update Yii you will need to make sure this still works, but at least its not directly modifying framework files

[ItsYii]

DrBobbyDylan, on 12 June 2012 - 09:03 PM, said:

I have an alternative solution that doesn't require modification of framework files (version 1.1.10.r3566)

Simply extend CActiveDataProvider and update your models to use this as the data provider

class ActiveDataProvider extends CActiveDataProvider
{
	/**
	 * Fetches the data from the persistent data storage.
	 * @return array list of data items
	 */
	protected function fetchData()
	{
		$criteria=clone $this->getCriteria();

		if(($pagination=$this->getPagination())!==false)
		{
			$pagination->setItemCount($this->getTotalItemCount());
			$pagination->applyLimit($criteria);
		}

		// update limit to the correct value for the last page 
		$limit=$pagination->getLimit();
		$offset=$pagination->getOffset();
		if ( $offset+$limit > $pagination->getItemCount() )
			$criteria->limit = $pagination->getItemCount() - $offset;
		
		$baseCriteria=$this->model->getDbCriteria(false);

		if(($sort=$this->getSort())!==false)
		{
			// set model criteria so that CSort can use its table alias setting
			if($baseCriteria!==null)
			{
				$c=clone $baseCriteria;
				$c->mergeWith($criteria);
				$this->model->setDbCriteria($c);
			}
			else
				$this->model->setDbCriteria($criteria);
			$sort->applyOrder($criteria);
		}

		$this->model->setDbCriteria($baseCriteria!==null ? clone $baseCriteria : null);
		$data=$this->model->findAll($criteria);
		$this->model->setDbCriteria($baseCriteria);  // restore original criteria
		return $data;
	}

}

Then update your models eg

public function search()
	{
		$criteria=new CDbCriteria;
		return new ActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}

Same solution for CSqlDataProvider.

class SqlDataProvider extends CSqlDataProvider
{

	/**
	 * Fetches the data from the persistent data storage.
	 * @return array list of data items
	 */
	protected function fetchData()
	{
		$sql=$this->sql;
		$db=$this->db===null ? Yii::app()->db : $this->db;
		$db->active=true;
	
		if(($sort=$this->getSort())!==false)
		{
			$order=$sort->getOrderBy();
			if(!empty($order))
			{
				if(preg_match('/\s+order\s+by\s+[\w\s,]+$/i',$sql))
					$sql.=', '.$order;
				else
					$sql.=' ORDER BY '.$order;
			}
		}
	
		if(($pagination=$this->getPagination())!==false)
		{
			$pagination->setItemCount($this->getTotalItemCount());
			$limit=$pagination->getLimit();
			$offset=$pagination->getOffset();
			
            // update limit to the correct value for the last page 
			if ( $offset+$limit > $pagination->getItemCount() )
				$limit = $pagination->getItemCount() - $offset;
			
			$sql=$db->getCommandBuilder()->applyLimit($sql,$limit,$offset);
		}
	
		$command=$db->createCommand($sql);
		foreach($this->params as $name=>$value)
			$command->bindValue($name,$value);
	
		return $command->queryAll();
	}
}

When you update Yii you will need to make sure this still works, but at least its not directly modifying framework files

That is fantastic, can we please include this on the docs, or the new version of Yii. I find the support for MSSQL on Yii is very weak at times.

Thank You DrBobbyDylan for the solution and Rohit Duhan for starting the thread.