Yii, Sort and column name in CsqlDataProvider

Question

I had trying to use cgridview with csqldataprovider.

1. my sql query takes data from different tables. I had used alias in query. So in gridview when i click on header value, it takes column name for sorting. i.e, column name = Track it will create query with ... order by Track but actually it should create ...order by t.trackname.
2. To add more complexity i have column name in table as table name. E.g In same db with table name Store and col name trackid i have another table in same db with table name trackid.
3. I had checked solution. Here in cgridview when I specify my column attribute it is not becoming sort-able (no anchor tags). Without column attribute it is sort-able.

In controller file,

$dataProvider=new CSqlDataProvider($query, array(
        'totalItemCount'=>$count,
        'sort'=>array('defaultOrder'=>'caseid DESC',
            'attributes'=>array(
             'caseid',
             'trackid',
             'status',
            ),
        ),
        ));

In view file,

$this->widget('zii.widgets.grid.CGridView', array(
'dataProvider'=>$dataProvider,
'ajaxUpdate'=>true,
'columns'=>array(
    array('header'=>'Case','name'=>'Case','value'=>'$data[caseid]'),
    array('header'=>'Track','name'=>'Track','value'=>'$data[trackid]'),
    array('header'=>'Status','name'=>'Status','value'=>'$data[status]'),
),

Answer 1

You have to specify the name for each column of CGridView correctly. In your sample code it is incorrect, you have used 'name'=>'Case' whereas it should be 'name'=>'caseid' , i.e the value of name should be the exact value of the alias you specify in your sql.

Update:

1. Also note that incase you are not doing any formatting, or modifying the value retrieved from db, you do not need the value attribute. The name attribute will take care of getting the correct value from the db result set. The header will take care of displaying the column header name, which in your case is Case.

   Example:

   'columns'=>array(
       array('header'=>'Case','name'=>'caseid'), // 'value'=>'$data[caseid]'),
       array('header'=>'Track','name'=>'trackid'), // 'value'=>'$data[trackid]'),
       array('header'=>'Status','name'=>'status'), // 'value'=>'$data[status]'),
   ),
   

2. To handle situations 1 and 2 use alias(es) in your sql, and use those aliases' values in sort's attributes array, and also in name.

   Sql example:

   select t.trackname as some_alias ...
   

   Dataprovider:

   'sort'=>array(
       'attributes'=>array(
           'some_alias',
           // ... more attributes ...
       ),
       // ... more options ...
   ),
   

   CGridView:

   'columns'=>array(
       array(
           'header'=>'Some Header Name',
           'name'=>'some_alias',
           'value'=>'$data["some_alias"]' // as mentioned above this is unnecessary if you are not modifying the value
       )
   )
   

   So when you sort, queries generated will be like : order by some_alias, which is perfectly fine.