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'));