[Solved] Sorting problem on the foreign key column

Hi all,

I have problem in sorting on the column that link to other model. These are my two models:




account

-------

id

accountNo

accountName

normalBalance

classificationFk  'is foreign key to classification(id)'

status


and 


classification

--------------

id

name

status




and below is CGridView definition:




<?php

    $this->widget('zii.widgets.grid.CGridView', array(

        'id' => 'account-grid',

        'dataProvider' => $model->search(),

        'filter' => $model,

        'emptyText' => 'Data tidak tersedia.',

        'columns' => array(

            array(

                'header' => 'No',

                'value' => '$this->grid->dataProvider->pagination->currentPage*$this->grid->dataProvider->pagination->pageSize + $row+1', //  row is zero based

                'htmlOptions' => array('style' => 'text-align:center;width: 30px;'),

            ),

            array(

                'name' => 'accountNo',

                'type' => 'raw',

                'value' => 'CHtml::link(CHtml::encode($data->accountNo), array("view", "id"=>$data->id,"page"=>$_GET[\'Account_page\']))',

                'htmlOptions' => array('class' => 'linkView', 'style' => 'text-align: center; width: 80px;'),

            ),

            'accountName',

            array(

                'name' => 'normalBalance',

                'type' => 'raw',

                'value' => 'Account::model()->getNormalBalanceName($data->normalBalance)',

                'filter' => Account::model()->getNormalBalanceOptions(),

                'htmlOptions' => array('style' => 'text-align: center; width: 70px;'),

            ),

            array(

                'name' => 'classification',

                'type' => 'raw',

                'value' => '$data->classification->name',

                'filter' => Account::model()->getClassificationOptions(),

                'htmlOptions' => array('style' => 'text-align: center; width: 125px;'),

            ),

            array(

                'name' => 'status',

                'type' => 'raw',

                'value' => '$data->status?CHtml::image("images/aktif.png"):CHtml::image("images/tidak_aktif.png")',

                'filter' => Account::model()->getStatusOptions(),

                'htmlOptions' => array('style' => 'text-align: center; width: 80px;'),

            ),

        ),

        'template' => '<div style="margin: 10px 0px; padding: 0px;">{pager}</div>{items}{pager}',

    ));

?>



and this is my model->seacrh()




 public function search($merge=null)

    {

            // Warning: Please modify the following code to remove attributes that

            // should not be searched.


            $criteria=new CDbCriteria;


            $criteria->compare('accountNo',$this->accountNo,true);

            $criteria->compare('accountName',$this->accountName,true);

            $criteria->compare('normalBalance',$this->normalBalance,true);

            $criteria->compare('classificationFk',$this->classificationFk);

            $criteria->compare('status',$this->status);


            $criteria->with= array('classification');

            

            $sort = new CSort();

            $sort->defaultOrder = 'accountNo ASC';

            $sort->attributes = array(

                'accountNo'=>'accountNo',

                'accountName'=>'accountName',

                'normalBalance'=>'normalBalance',

                'classification' => array(

                    'asc'=>'classification.name',

                    'desc'=>'classification.name DESC',

                    'label'=>'Classification'

                ),

                'status'=>'status',

            );


            $sort->applyOrder( $criteria );


            return new CActiveDataProvider(get_class($this), array(

                    'criteria'=>$criteria,

                    'sort'=>$sort,

                    'pagination'=>array(

                        'pageSize'=> Yii::app()->user->getState('accountPageSize',

                                        Yii::app()->params['defaultPageSize']),

                    ),

            ));

    }



If I put $criteria->with= array(‘classification’);, I will get this error message:

“CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘status’ in where clause is ambiguous”

If I remove this line, I will have javascript alert mentioning error on unknown column classification.name.

Anyone can help?

Also my other problem is can I keep the currently sort selection?

Cheers,

Daniel

Change


$criteria->compare('status',$this->status);

to


$criteria->compare('t.status',$this->status);

In order to resolve the ambiguity.

Also you have to update the sort accordingly.

Hi Zac,

Thank you. it works like a charm.

Cheers,

Daniel

thanks dude… i was struggling for a long time … thumbs up

Wow! I added the solution as a comment to the documentation. It talks about disambiguation but I couldn’t get it to work and this does! Thanks zaccaria!