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

'status_id'

to

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):

$data->status_id

to

$data->status_relation->name

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

'status_id'

with

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('t.id',$this->id);
  $criteria->compare('status_relation.name',$this->status_id, true);

  //load the related table at the same time:
  $criteria->with=array('status_relation');

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

change

$form->textField($model,'status_id');

to

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

22 comments:

  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"?

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

    ReplyDelete
    Replies
    1. Remove this part:
      $criteria->compare('t.id',$this->id);

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

    ReplyDelete
  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 :
    array('name'=>'role_number',
    'header'=>'Role Name',
    'value'=>'$data->role->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?

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

      in RoleModel:
      'relUser'=>array(self::HAS_MANY,'User','role_number'),

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

      Delete
  5. Hey,

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

    P.S. Keep posting!

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

    http://www.yiiframework.com/forum/index.php?/topic/12742-solved-filtering-in-advanced-search-fails-for-values-from-relation/page__p__62381__hl__advanced+earch+relation#entry62381

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

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

    ReplyDelete
  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 :)

    ReplyDelete
  10. Puts things together! Thanks!

    ReplyDelete
  11. 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!

    ReplyDelete
  12. Omg! Very helpful :) thanks a lot!

    ReplyDelete
  13. 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???

    ReplyDelete
  14. it's work!!

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

    Great Post->Keep posting!

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

    ReplyDelete
  16. Wwowowowow !!!

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

    Big thanks!

    ReplyDelete
  17. A million great for you...

    It works well

    ReplyDelete