How To Sort The Fields From Relation Table In Yii

Hi,

Actually this question has been asked several times before. But unfortunately I still could not achieve the expected result even after tried so hard. So I am posting this here because someone can identify where I got wrong.

Basically I have 2 tables user and address. In user table I store user’s first name, last name etc. and in address table company name, address, city etc… The relationship between 2 tables shows below.

And the view is given below.

In the above view company field cannot be sorted. I want it to be sorted.

model of the user table is given below.




class User extends CActiveRecord

{

	/**

	 * Returns the static model of the specified AR class.

	 * @param string $className active record class name.

	 * @return User the static model class

	 */

    

    public $confirm_email;

    public $confirm_password;

    public $new_password;

    public $company;




    public $agree=0;

    

	public static function model($className=__CLASS__)

	{

		return parent::model($className);

	}


	/**

	 * @return string the associated database table name

	 */

	public function tableName()

	{

		return 'user';

	}


	/**

	 * @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('title, first_name, last_name, 

			

            array('how_did_you_hear, describe_business, estimated_purchase, client_before, confirm_email, address, company, user_id', 'safe'),

		);

	}


	/**

	 * @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(

			

			'address' => array(self::HAS_ONE, 'Address', 'user_id'),

			

			'authassignment' => array(self::HAS_ONE, 'Authassignment', 'userid'),

			

		);

	}


	/**

	 * @return array customized attribute labels (name=>label)

	 */

	public function attributeLabels()

	{

		return array(

			'customer_id' => 'Customer',

			'title' => 'Title',

			'first_name' => 'First Name',

			'last_name' => 'Last Name',

		//	'user_name' => 'User Name',

			'password' => 'Password',

               'confirm_password' => 'Confirm Password',

			'email' => 'Email',


               

			'date_added' => 'Date Added',

			'date_updated' => 'Date Updated',

			'status' => 'Status',

			

            

		);

	}


	

	

     public function search_customer()

     {

          $criteria=new CDbCriteria;

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

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

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

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

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

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


        

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

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

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

          

          $criteria->together = true;

          

          $criteria->condition = ('authassignment.itemname="xxxxx"');                                            

          $criteria->with = array('address', 'authassignment');            

         

          $criteria->compare('address.company', $this->company, true);

          

          return new CActiveDataProvider($this, array(

               'criteria'=>$criteria,

               'sort'=> array(

                'attributes' => array(

                    'company' => array(

                        'asc' => 'address.company ASC',

                        'desc' => 'address.company DESC'

                 ),

                 '*',

                ),

              )

          )); 

     }



In above code I only added necessary parts of my actual User model. Of that note following point

  1. Add $company as a public property in User.php

  2. Add company under rules in ‘safe’ section

  3. Add $criteria->with = array(‘address’, ‘authassignment’); in customer search

Below is UserController.php Only necessary part.




/**

	 * Manages all models.

	 */

	public function actionAdmin()

	{

		$model=new User('search_customer');

		$model->unsetAttributes();  // clear any default values

		if(isset($_GET['User']))

			$model->attributes=$_GET['User'];


		$this->render('admin',array(

			'model'=>$model,

		));

	}



and admin.php(view)




<?php $this->widget('bootstrap.widgets.TbGridView', array(

                    'id'=>'user-grid',

                    'type' => array('striped', 'bordered', 'condensed','hover'),

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

                    'filter'=>$model,    

                    'columns'=>array(

                       array(

                           'id'=>'user_id',

                           'class'=>'CCheckBoxColumn',

                           'selectableRows' => '50', 

                           'selectableRows'=>2,

                           'value'=>$model->user_id

                       ),

                         'email',       

                         'first_name',

                         'last_name',

                         array(

                            'name' => 'address.company',                            

                            'filter'=>CHtml::activeTextField($model,'company'),

                         ),

//                         'address.company',

                         'address.postcode',

                         'address.telephone',

                         'address.mobile',

                         'authassignment.itemname', 

                                                  

                         //'user_name',

                         array(

                             'class'=>'bootstrap.widgets.TbButtonColumn',

                             'template' => '{view} &nbsp;&nbsp; {update} &nbsp;&nbsp;{delete}',


                         ),

                    ),

               )); ?>



And the links which I got instructions given below.

http://www.yiiframework.com/wiki/281/searching-and-sorting-by-related-model-in-cgridview/

http://www.yiiframework.com/forum/index.php/topic/20442-model-search-criteria-on-related-record/

http://www.mrsoundless.com/php/yii/searching-and-sorting-a-column-from-a-related-table-in-a-cgridview/

http://www.yiiframework.com/forum/index.php?/topic/9083-search-filter-of-a-relations-field-through-cgridview/

If any one can help on this matter it would be very grateful.

I hope this page solution will help you to solve your problems

http://www.yiiframework.com/forum/index.php/topic/8148-cgridview-filter-with-relations/

Hi Chandran,

Thanks for the reference of the post. It is a really nice post regarding this issue. But the problem is when I try to

add




'value' => '$data->address->company',



in the view it gives error ‘[size=“4”]Trying to get property of non-object[/size]’.




<?php $this->widget('bootstrap.widgets.TbGridView', array(

                    'id'=>'user-grid',

                    'type' => array('striped', 'bordered', 'condensed','hover'),

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

                    'filter'=>$model,    

                    'columns'=>array(

                       array(

                           'id'=>'user_id',

                           'class'=>'CCheckBoxColumn',

                           'selectableRows' => '50', 

                           'selectableRows'=>2,

                           'value'=>$model->user_id

                       ),

                         'email',       

                         'first_name',

                         'last_name',

                         array(

                            'name' => 'company',                            

//                          'filter'=>CHtml::activeTextField($model,'company')  

                            'value' => '$data->address->company',                 // Gives an error

                         ),

//                         'address.company',

                         'address.postcode',

                         'address.telephone',

                         'address.mobile',

                         'authassignment.itemname',  

                                                  

                         //'user_name',

                         array(

                             'class'=>'bootstrap.widgets.TbButtonColumn',

                             'template' => '{view} &nbsp;&nbsp; {update} &nbsp;&nbsp;{delete}',


                         ),

                    ),

               )); ?> 



Do you have an idea what is the cause of this error?

hi, if u want to make relation between these two tables, u can write relation in model itself…like

In Users model u can do like this…





class Users

{

public function relations()

	{

		return array(

		

'address'=>array(self::BELONGS_TO, 'Address', 'user_id'),

			

		

		);

	}





}




]

and in UserController…





$model = Users:model()->with('address')->findByPK('user_id');

echo $model->address->Company; //here u can access like this




Hi

I recommend that you check my extension: www.yiiframework.com/extension/relatedsearchbehavior/ which is very usefull for this.

I agree with Mannu, you should use




'address'=>array(self::BELONGS_TO,'Address', 'user_id'),



Hi all,

Thanks for the replies. I change the coding accordingly but still it says "Trying to get property of non-object".




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(

			//'tenant' => array(self::BELONGS_TO, 'Tenant', 'tenant_id'),

			//'address' => array(self::HAS_ONE, 'Address', 'user_id'),

			'address'=>array(self::BELONGS_TO,'Address', 'user_id'),

                        //'authassignment' => array(self::HAS_ONE, 'Authassignment',       'userid','condition'=>'authassignment.itemname="Corporate.Admin"'),

			'authassignment' => array(self::HAS_ONE, 'Authassignment', 'userid'),

			 //'quotation'=>array(self::BELONGS_TO, 'Quotation', 'quotation_id'),

		);

	}



i think u sud check ur model name…

Ah, I see your issue. It’s data issue basically.

Please check your data, and make sure all users has Address data and authassignment data.

If not, then authassignment will return null, so you cannot use ‘authassignment.itemname’ in cgridview.

In this case, you can use this to replace your code:




array(

'header'=>"Item Name"

'value'=>"$data->authassignment?$data->authassignment->itemname:''",

),