CSqlDataProvider in CGridView

In this wiki I will show how could use a CSqlDataProvider in CGridView with specific features

In your controller/action

//a sample query but you could use more complex than it
$sql = 'SELECT DISTINCT T1.id AS MAIN_ID,T2.title AS title,T3.type AS type FROM T1 INNER JOIN T2 ON T1.id=T2.t1_id INNER JOIN T3 ON T2.id=T3.t2_id';

$rawData = Yii::app()->db->createCommand($sql); //or use ->queryAll(); in CArrayDataProvider
$count = Yii::app()->db->createCommand('SELECT COUNT(*) FROM (' . $sql . ') as count_alias')->queryScalar(); //the count

        $model = new CSqlDataProvider($rawData, array( //or $model=new CArrayDataProvider($rawData, array(... //using with querAll...
                    'keyField' => 'MAIN_ID', 
                    'totalItemCount' => $count,
                    //if the command above use PDO parameters
                    'sort' => array(
                        'attributes' => array(
                            'MAIN_ID','title', 'type'
                        'defaultOrder' => array(
                            'MAIN_ID' => CSort::SORT_ASC, //default sort value
                    'pagination' => array(
                        'pageSize' => 10,

        $this->render('anActionView', array(
            'model' => $model,

In your view file (anActionView.php)

$this->widget('zii.widgets.grid.CGridView', array(
    'id' => 'a-grid-id',
    'dataProvider' => $model,
    'ajaxUpdate' => true, //false if you want to reload aentire page (useful if sorting has an effect to other widgets)
    'filter' => null, //if not exist search filters
    'columns' => array(
            'header' => 'The id',
            'name' => 'MAIN_ID',
            //'value'=>'$data["MAIN_ID"]', //in the case we want something custom
            'header' => 'title',
            'name' => 'title',
            //'value'=>'$data["title"]', //in the case we want something custom
        'type', //just use it in default way (but still we could use array(header,name)... )
        array( //we have to change the default url of the button(s)(Yii by default use $data->id.. but $data in our case is an array...)
            'class' => 'CButtonColumn',
            'template' => '{delete}',
            'buttons' => array(
                'delete' => array('url' => '$this->grid->controller->createUrl("delete",array("id"=>$data["MAIN_ID"]))'),

That's it ;)