Cgridview And Pagination Offset Problem

Yii 1.1 General Discussion
1

i got the following dataprovider:




        $command = Yii::app()->db->createCommand();

        $command->select = '

            __t.Time AS Time,

            __t.DeviceID AS DeviceID,

            __t.LogFileID AS LogFileID,

            SensorA,

            /* more columns */

        ';

        $command->from = '(

            SELECT __s.Time, __s.DeviceID , __s.LogFileID FROM SensorLog    __s WHERE __s.DeviceID = "'.$this->__DeviceID.'" UNION

            SELECT __l.Time, __l.DeviceID , __l.LogFileID FROM Location     __l WHERE __l.DeviceID = "'.$this->__DeviceID.'" UNION

            SELECT __a.Time, __a.DeviceID , __a.LogFileID FROM Accelerator  __a WHERE __a.DeviceID = "'.$this->__DeviceID.'" ORDER BY TIME DESC LIMIT 50 '.

                (isset($_REQUEST['page']) ? 'OFFSET '.(50 * ($_REQUEST['page']-1)) : '').'

        ) as __t';

        $command->join = '

            LEFT JOIN SensorLog     ON SensorLog.Time   = __t.Time

            LEFT JOIN Location      ON Location.Time    = __t.Time

            LEFT JOIN Accelerator   ON Accelerator.Time = __t.Time

        ';

        $command->where = $criteria->condition;

        $command->params = $criteria->params;


        //count the total number of rows

        $count_command = Yii::app()->db->createCommand();

        $count_command->select = 'SUM( __count )';

        $count_command->from = '(

            SELECT COUNT(*) AS __count FROM SensorLog    __s WHERE __s.DeviceID = "'.$this->__DeviceID .'" UNION

            SELECT COUNT(*) AS __count FROM Location     __l WHERE __l.DeviceID = "'.$this->__DeviceID .'" UNION

            SELECT COUNT(*) AS __count FROM Accelerator  __a WHERE __a.DeviceID = "'.$this->__DeviceID .'"

        ) as __t';

        $count = Yii::app()->db->createCommand($count_command->text)->queryScalar();


        //create the dataprovider

        return new CSqlDataProvider($command,array(

            'keyField'=>'Time',

            'totalItemCount'=>$count,

            'sort'=>$sort,

            'pagination'=> array(

                'pageSize'=>50,

            )

        ));

The problem is with the pagination inside my gridview.

I don’t need the offset at the end of my sql query, i inserted it manually, hw do i prevent the OFFSET parameter from being addaed at the end of the query???

2

Is there any reason why you need to add offset manually? It’s usually best to let cgridview handle it if you’re using pagination.

3

There is/was a bug with the OFFSET in MSSQL. Here is a link to my bug reference which points to my fix:

http://www.yiiframew…inator-problem/

However, I just see that this bug is not your issue, however it hints me to a solution:

you can create your own CPagination class and set this as the ‘pagination’ parameter.

If your paginator returns ‘0’ in ‘getOffset’, the ‘OFFSET’ parameter is not added as this code shows:





	public function applyLimit($sql,$limit,$offset)

	{

		if($limit>=0)

			$sql.=' LIMIT '.(int)$limit;

		if($offset>0)

			$sql.=' OFFSET '.(int)$offset;

		return $sql;

	}