CActiveDataProvider With Custom Query

Hi i used CActiveDataProvider everytime to retrieve the data for CGridView.But one of my project needed custom query in criteria and have to show the result in CGridView. During that time i found MyActiveDataProvider concept with extends of CActiveDataProvider. I applied that concept and working fine for me. I added that source code for you. I think it may helpful on sometime.

Component MyActiveDataProvider

Please add this class inside “protected/components”.

<?php
class MyActiveDataProvider extends CActiveDataProvider {

    protected function calculateTotalItemCount() {
        $baseCriteria=$this->model->getDbCriteria(false);
        if ($baseCriteria!==null)
                $baseCriteria=clone $baseCriteria;
        //You can get real records count only in this way (when you use JOIN and GROUP BY)
        $count=count($this->model->findAll($this->getCriteria()));
        $this->model->setDbCriteria($baseCriteria);
        return $count;
    }
}
?>

Custom Query In Model

In this model i added the “MyActiveDataProvider” instead of “CActiveDataProvider” with custom select query.

<?php
    public function searchbygroup()
	{
		$criteria=new CDbCriteria;       
		//custom query
        $criteria->select=array('*,concat(documentid,"_",userid) as groupid,count(documentid) as downloadcount');
		//Add Different Condition
		$criteria->compare('userid',$this->userid,true);
        $criteria->addCondition("documentid IN ('$this->documentid')");
		$criteria->addBetweenCondition('createdon',$this->datefrom,$this->dateto);
        // group by , if need
        $criteria->group='groupid';
		//return new CActiveDataProvider($this, array(			'criteria'=>$criteria,		));
        return new MyActiveDataProvider($this,array('criteria'=>$criteria));
	}
?>

CGridview List Of Group Search

In this gridview i retrieved the data from custom query of CActiveDataProvider(via MyActiveDataProvider) and displayed in gridview result successfully.

<?php $this->widget('zii.widgets.grid.CGridView', array(
	'id'=>'downloadlog-grid',
	'dataProvider'=>$model->searchbygroup(),

	'columns'=>array(
		// columns from relation model
		array(
			'name'=>'documentid',
			'value'=>'$data->document->documentname',
		),
		array(
			'name'=>'userid',
            'value'=>'$data->user->username',
        ),
		// column from custom query
		'downloadcount',
	),
    'itemsCssClass'=>'table',
)); ?>

Leave a Reply

Your email address will not be published. Required fields are marked *