Yii Framework Forum: [ Solved ]CgridView Last Page Pagination / MsSql - Yii Framework Forum

Jump to content

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

[ Solved ]CgridView Last Page Pagination / MsSql Rate Topic: -----

#1 User is offline   Rohit Duhan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 25
  • Joined: 07-September 11

Posted 12 September 2011 - 10:49 PM

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 :)
0

#2 User is offline   Rohit Duhan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 25
  • Joined: 07-September 11

Posted 29 September 2011 - 04:25 PM

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 ????
0

#3 User is offline   Rohit Duhan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 25
  • Joined: 07-September 11

Posted 03 October 2011 - 04:38 PM

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... :(
0

#4 User is offline   wawancell 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 28-January 10

Posted 04 January 2012 - 08:17 PM

View PostRohit 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 :)
0

#5 User is offline   vario 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 36
  • Joined: 03-January 10

Posted 12 January 2012 - 12:34 PM

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

#6 User is offline   Rohit Duhan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 25
  • Joined: 07-September 11

Posted 13 January 2012 - 11:27 AM

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

0

#7 User is offline   DrBobbyDylan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 02-April 12

Posted 12 June 2012 - 09:03 PM

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 :blink:
1

#8 User is offline   ItsYii 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 118
  • Joined: 24-January 12

Posted 18 November 2012 - 07:06 AM

View PostDrBobbyDylan, 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 :blink:


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

#9 User is offline   admerre 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 27-November 11

Posted 20 December 2013 - 05:23 AM

You have a little mistake in your code. If I need to set pagination to false (in some causes), it will throw a error. It should be included more strings in if-pagination-not-false-condition:


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


Thank you for the solution)
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