Yii 1.1: 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 ;)

Total 8 comments

#16934 report it
KonApaz at 2014/04/14 09:02am
RE: #16875

Thanks yiqing95,

Your function it could be used in most cases, reducing the resource consumption of the sql execution.

#16875 report it
yiqing95 at 2014/04/06 04:27am
now i using another way to calculate the count
     * @static
     * @param $sql
     * @param array $params
     * @param CDbConnection $db
     * @return int|mixed
     * @warning id the sql string has "UNION" and is in the sub query you 'd better
     *    not use this function !
     * if the sql not contain "UNION" it will work well !
    static public function countBySql($sql, $params = array(), CDbConnection $db = null)
        $parts = explode('UNION', $sql);
        if (count($parts) > 1) {
            $count = 0;
            foreach ($parts as $selectSql) {
                $count += self::countBySql($selectSql, $params);
            return $count;
        } else {
            $selectStr = trim($sql); //
            $selectStr = substr_replace($selectStr, ' COUNT(*) ', 6, stripos($selectStr,
                'FROM') - 6);
            $selectStr = preg_replace('~ORDER\s+BY.*?$~sDi', '', $selectStr);
            $db = ($db == null) ? Yii::app()->db : $db;
            return $db->createCommand($selectStr)->queryScalar($params);

note : if your query string contain more then one union clause or union clause exists in subquery , plz do not use this

#16873 report it
KonApaz at 2014/04/05 03:47pm
RE: #16872

the main query returns all the specific rows.

but in CSqlDataProvider (when pagination is used), limits concatenates in the main in the final query (for example select * from... limit 0,10). In this case CSqlDataProvider cannot calculate the number of pages pagination (and the current page), so you have to set the totalItemCount explicity.


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

using to calculates the total counts

If anyone knows a more efficient way, please inform us.

#16872 report it
yiqing95 at 2014/04/05 08:28am
want to know if the counting is a efficient way

SELECT COUNT(*) FROM (' . $sql . ') as count_alias

This way using a subquery , so just want to know whether this is a efficient way .

#16833 report it
KonApaz at 2014/04/01 11:37am
RE: #16831

Is the same thing

$rawData = Yii::app()->db->createCommand($sql); //or use ->queryAll(); in CArrayDataProvider

Yii::app()->db->createCommand($sql) does not return array of data. I named it '$rawdata' for alternative CArrayDataProvider code

#16831 report it
neophyte at 2014/04/01 10:45am
Use of $rawdata
$model = new CSqlDataProvider($rawData, array()

Why use $rawData here can't we use $sql? Also I don't think you need this line

$rawData = Yii::app()->db->createCommand($sql);
#16830 report it
KonApaz at 2014/04/01 09:23am
RE: #16829

it was a typo

'dataProvider' => $model

'$data' is Yii-CGridview internal iterator that takes each item of the $model.

if you want see more details about all the data of the CSqlDataProvider You could type

var_dump ($model->getData());
#16829 report it
neophyte at 2014/04/01 09:07am

'dataProvider' => $main, What is $main here??

//'value'=>'$data["MAIN_ID"]', //in the case we want something custom , What is $data here??

Leave a comment

Please to leave your comment.