Accessing Field In Related Table Through Foreign Key In Yii

Hi guys, I have three tables

  • tbl_employee: id(PK), name, position_id(FK), type_id(FK)

  • tbl_position: id, position

  • tbl_type : id, type

I want to display records in field position like what sql does below.


SELECT tbl_employee.name, tbl_position.position

FROM tbl_employee, tbl_position

WHERE tbl_employee.position_id = tbl_position.id AND tbl_position.position LIKE '%Designer';

In my EmployeeController


public function actionIndex()

	{

		$dataProvider=new CActiveDataProvider('Employee', array(

			'criteria'=>array(

				'select'=>'t.name, tbl_position',

				'with'=>array(

					'position'=>array('select'=>'position'),

					'type'=>array('select'=>'type'),

				),

				'condition'=>"tbl_position.position LIKE '%Designer'",

				

			),		


		));

		$this->render('index',array('dataProvider'=>$dataProvider,));

	}

and in my models


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(

			'position' => array(self::BELONGS_TO, 'Position', 'position_id'),

			'type' => array(self::BELONGS_TO, 'Type', 'type_id'),

		);

	}



and this is my view


<?php $this->widget('zii.widgets.CDetailView', array(

	'data'=>$model,

	'attributes'=>array(

		'id',

		'name',

		array(

			'name'=>'position_id',

			'value'=>CHtml::encode($model->position->position),

		),

		array(

			'name'=>'type_id',

			'value'=>CHtml::encode($model->type->type),

		)

	),

)); ?>

The error I’ve got is




Active record "Employee" is trying to select an invalid column "tbl_position". 

Note, the column must exist in the table or be an expression with alias. 



How can I access position field in tbl_position by using join?What is the correct syntax for achieving that purpose.

Many thanks

Finally I managed to solve it by changing employee controller to





public function actionIndex(){

   $dataProvider=new CActiveDataProvider('Employee', array(

      'criteria'=>array(

      'select'=>'t.name',

      'with'=>array(

         'position'=>array('select'=>'position'),

         'type'=>array('select'=>'type'),

      ),

      'condition'=>"position.position LIKE '%Designer'",

    ),));

    $this->render('index',array('dataProvider'=>$dataProvider,));

}