Yii Framework Forum: [Solved] Sorting problem on the foreign key column - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

[Solved] Sorting problem on the foreign key column Rate Topic: -----

#1 User is offline   Daniel 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 222
  • Joined: 26-September 09

Posted 09 March 2011 - 03:30 AM

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
0

#2 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 09 March 2011 - 03:51 AM

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.
2

#3 User is offline   Daniel 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 222
  • Joined: 26-September 09

Posted 09 March 2011 - 06:09 AM

Hi Zac,

Thank you. it works like a charm.

Cheers,

Daniel

View Postzaccaria, on 09 March 2011 - 03:51 AM, said:

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.

0

#4 User is offline   jerome nicholas 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 18-April 11

Posted 08 June 2012 - 07:07 AM

View Postzaccaria, on 09 March 2011 - 03:51 AM, said:

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.


thanks dude.. i was struggling for a long time .... thumbs up
Jerome Nicholas
Software Developer
Shalom Software, Melbourne
http://www.shalomsoftware.com.au
0

#5 User is offline   SebK 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 40
  • Joined: 19-April 12

Posted 08 June 2012 - 09:47 AM

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!
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users