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