How to make CGridView column sortable using CSqldataprovider?

Question

How to make CGridView columns sortable (On clicking column title) using CSqldataprovider.

In controller

    $sql = "select id ,name, address
            from User
            where city = 'ABC' ";
    $rawData = Yii::app()->db->createCommand($sql);
    return $allMovies = new CSqlDataProvider($rawData, array(
        'keyField' => 'id',
        'sort'=>array(
            'attributes'=>array(
            'id', 'name', 'address',
          ),
        ),
        'pagination' => array(
                'pageSize' => 10,
            ),
    ));

In view

 $this->widget('zii.widgets.grid.CGridView', array(
    'id' => 'all_movies',
    'dataProvider' => $allMoviesStats,
    'columns' => array(
            'id',
            'name', 
            'address',
            'city'
      )
    )
);?>

It is giving error

 "error":{"code":99,"text":"Property \"CGridView.sort\" is not defined.

Answer 1

You need to create object for CDbCriteria and Sort class and try to use as below and change the code for your requirement.

public function search()
{
    $criteria=new CDbCriteria;
    $criteria->condition="active=1";
    if($this->name=="Enter Country Name" || $this->name=='') {
        $this->name='';
    } else {
        $this->name=$this->name;
    }
    $criteria->compare('name',$this->name,true);
    $sort = new CSort;
    $sort->defaultOrder = 'id DESC';
    $sort->attributes = array(          
        'name' => array(
            'asc'   =>'name',
            'desc'  =>'name DESC',
        ),
        ...
        ... // attributes to sort
    );
    return new CActiveDataProvider('Country', array( //Country is nothing but you model class name
        'criteria'  =>$criteria,
        'sort'       => $sort,
        'pagination'=>array('pageSize'=> 10),
    ));
}