Yii : Can't Sort CGridView Column When Using Database View as the Model

Question

I have a problem with CGridView in Yii.

If I use Table as the model, then the CGridView is working normally. It can be sorted and filtered.

But if I use View as the model, the CGridView can't be sorted..

Please help, Thanks

Below is my code

index.php

<?php $this->widget('zii.widgets.grid.CGridView', array(
'id'=>'unit-history-grid',
'cssFile' => Yii::app()->request->baseUrl . '/css/gridview.css',
'dataProvider'=>$model->search(),
'filter'=>$model,
'enableSorting'=>false,
'columns'=>array(
    'serial_no',
    'customer_name',
    'visit_count',
    'startup_serviceman',
    array(
        'header' => 'Startup Date',
        'name' => 'startup_date',
        'type' => 'raw',
        'value' => 'AppHelper::formatDate($data->startup_date)',
        'filter' => false,
    ),
    array(
        'header' => '',
        'type' => 'raw',
        'value' => '"<a href=\"'. Yii::app()->request->baseUrl .'/inquiry/unitHistory/visit/serial_no/". $data->serial_no ."\">History Visit</a>"',
    ),
    array(
        'header' => '',
        'type' => 'raw',
        'value' => '"<a href=\"'. Yii::app()->request->baseUrl .'/inquiry/unitHistory/spareParts/serial_no/". $data->serial_no ."\">History Recommended Parts</a>"',
    ),      
),
)); ?>

My model : ViewUnitHistory.php

    class ViewUnitHistory extends CActiveRecord
{
    /**
     * Returns the static model of the specified AR class.
     * @return ViewUnitHistory the static model class
     */
    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }
/**
 * @return string the associated database table name
 */
public function tableName()
{
    return 'view_unit_history';
}

public function primaryKey(){
    return 'serial_no';
}

/**
 * @return array validation rules for model attributes.
 */
public function rules()
{
    // NOTE: you should only define rules for those attributes that
    // will receive user inputs.
    return array(
        array('serial_no, customer_name', 'required'),
        array('serial_no', 'length', 'max'=>30),
        array('customer_name', 'length', 'max'=>100),
        array('visit_count', 'length', 'max'=>21),
        array('startup_date, startup_serviceman', 'safe'),
        // The following rule is used by search().
        // Please remove those attributes that should not be searched.
        array('serial_no, customer_name, visit_count, startup_date, startup_serviceman', 'safe', 'on'=>'search'),
    );
}

/**
 * @return array relational rules.
 */
public function relations()
{
    // NOTE: you may need to adjust the relation name and the related
    // class name for the relations automatically generated below.
    return array(
        'customerProduct' => array(self::BELONGS_TO, 'CustomerProduct', 'serial_no'),
        'userCreate' => array(self::BELONGS_TO, 'User', 'created_by'),
        'userModify' => array(self::BELONGS_TO, 'User', 'modified_by'),
    );
}

/**
 * @return array customized attribute labels (name=>label)
 */
public function attributeLabels()
{
    return array(
        'serial_no' => 'Serial No',
        'customer_name' => 'Customer Name',
        'visit_count' => 'Visit Count',
        'startup_date' => 'Startup Date',
        'startup_serviceman' => 'Startup Serviceman',
    );
}

/**
 * Retrieves a list of models based on the current search/filter conditions.
 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
 */
public function search()
{
    // Warning: Please modify the following code to remove attributes that
    // should not be searched.

    $criteria=new CDbCriteria;

    $criteria->compare('t.serial_no',$this->serial_no,true);
    $criteria->compare('t.customer_name',$this->customer_name,true);
    $criteria->compare('t.visit_count',$this->visit_count,true);
    $criteria->compare('t.startup_date',$this->startup_date,true);
    $criteria->compare('t.startup_serviceman',$this->startup_serviceman,true);

    $user = User::model()->findByPk(Yii::app()->user->getState('user_id'));
    if ($user->branch_id != NULL) {
        $criteria->addCondition('a.branch_id = ' . $user->branch_id);
    }

    $criteria->with = array(
        'customerProduct.customer' => array('alias'=>'a'),
    );

    return new CActiveDataProvider(get_class($this), array(
        'criteria'=>$criteria,
        'sort'=>array(
            'attributes'=>array(
                'serial_no'=>array(
                    'asc'=>'t.serial_no',
                    'desc'=>'t.serial_no DESC',
                ),
                'customer_name'=>array(
                    'asc'=>'t.customer_name',
                    'desc'=>'t.customer_name DESC'
                ),
            ),
        ),
    ));
}
}

Below is actionIndex function in my Controller

/**
     * Lists all models.
     */
    public function actionIndex()
    {
        $model=new ViewUnitHistory('search');
        $model->unsetAttributes();  // clear any default values
        if(isset($_GET['ViewUnitHistory']))
            $model->attributes=$_GET['ViewUnitHistory'];

        $this->render('index',array(
            'model'=>$model,
        ));
    }

Answer 1

to be honest i don't really understand where the problem whit sorting is. i'm just gonna point the obvious

in the CGridView widget 'enableSorting'=>false

the grid is unsortable while that attribute is set to false. if you can't autosort it when generating the grid i suppose it wouldn't hurt just ordering it the way you want since its unsortable anyway.

to do this just do

$criteria->with = array(
        'customerProduct.customer' => array('alias'=>'a'),
    );

$criteria->order = 't.serial_no DESC';

the sort array in CActiveDataProvider tries to sort the grid which has the 'enableSorting' attribute set to false so ofcourse it can't do it.

if you want to sort the grid just enable sorting, if you don't do the order in criteria which will return the results sorted by the db in your desired order.

hope this helps

Answer 2

I have just tested this and it works just fine. I was able to generate with Gii full crud based on a view (I had to define the primary key for the model but the rest went great).

Now I would take a look at what 'enableSorting'=>false does, that might be it :).

Answer 3

brother can i see controller? basically what I think issue is that you use this view with your model. BUT you replace it with your fact table or database view. Now what happens that there are not all columns present in that Db view that you are pointing in CGRidView