Thursday, January 6, 2011

Using relations in views

[this is copied and edited from my post on stackoverflow]

Relations are easier if you setup a foreign key in your database first. To do this you need to use MySQL (not SQLite) with the InnoDB engine (not MyISAM), and the field in question needs an index and foreign key on it. Then, Gii will setup the relations function for you automatically. Otherwise, you'll have to do it manually in the relations() function of the model in question.

Imagine if you will, you have an integer field "status_id" on an order table, that relates to a status table "status_table", that has a column "name". You want to show the related "name", and not the status id.

To use a related value in a View:

In protected/views/[name]/view.php, in the CDetailView attributes array, change



array('label'=>'Status', 'value'=>$model->status_relation->name)

where status_relation is the name of the relation defined in the model

To use a related value in an Index view, change protected/views/[name]/_view.php (note the underscore):




To use a related value in an Admin view, in the CGridView widget function, in the columns array, replace



array('name'=>'status_id', 'header'=>'Status', 'value'=>'$data->status_relation->name')

(note the use of the variable $data, and not say $model or $dataProvider, and the quotes around $data).

In your model's search function:
public function search()

  $criteria=new CDbCriteria;

  //add the magic letter 't' to refer to the 'main' (not the related) table:
  $criteria->compare('',$this->status_id, true);

  //load the related table at the same time:

  return new CActiveDataProvider(get_class($this), array('criteria'=>$criteria,));
To use a drop-down menu, in protected/views/[name]/_form.php:




$form->dropDownList($model,'status_id', CHtml::listData(StatusModel::model()->findAll(), 'status_id', 'name'));


  1. Thank you very much, very clear and helpful,
    but what you mean by :
    "add the magic letter 't' to refer to the 'main' (not the related) table"?

  2. It doesn't work. In that code the will be compared with
    $this->status_id during search, which is not what we want. Please, provide correct version.

    1. Remove this part:

  3. thanks for sharing, got me out of a jam

  4. Your code is an inspiration man.
    but i have some problems when changing the CGridView.

    Property "User.role_name" is not defined.
    short review of the script :
    'header'=>'Role Name',
    and the table is User with column id.
    and relate to table role with column that i want to retrieve is role_name.

    can you please help me,,is there something wrong with my code?

    1. Please check your relations.
      in User :
      'relRole'=>array(self::BELONGS_TO, 'RoleModel','role_number'),

      in RoleModel:

      in view:
      array('name'=>'role_number','header'=>'Role Name','value'=>'$data->relRole->role_name',),

  5. Hey,

    I would like to thank you for this; great post.

    P.S. Keep posting!

  6. I would like to post a referring link that will aid this post.

    The following link will provide the answers to the questions above with respect to _search form and CGrid column settings. Enjoy!

  7. Beautiful!
    Had scanned the forums and failed to find a simple, precise and compacted way to deal with relations.
    Contemplate producing a book Neil.

  8. How do you do the filter in cGridView...?

  9. Thank You Soooo Much i have been looking for this for days and this is the best tutorial i got u have explained this nicely thumbs up :)

  10. You are my hero!

  11. mast !! thanks !!

  12. Puts things together! Thanks!

  13. Thanks - I had spent quite a while trying to get this to work. Your post made it totally clear and generally made Yii a lot clearer. I'm now flying!

  14. Omg! Very helpful :) thanks a lot!

  15. i have a problem in yii relations..1st table is places where i put the name of places and 2nd table is images where i put place_id and images how i join it in yii???

  16. it's work!!

    I would like to thank you, a millions... :)
    Good Job!
    Very helpful!! You are my hero!

    Great Post->Keep posting!

  17. I would like to thank you for the wonderful sharing.
    It really helps so much. Please continue posting....

  18. Wwowowowow !!!

    You've saved me a day of pointless trials and errors !

    Big thanks!

  19. A million great for you...

    It works well