three tables relationship

Hello.

Can sombody help me to establish correct relations among three tables?

My tables are

  1. teachers

---------------------------------

| pedid |    name    | schoolid |

---------------------------------

| 1     |teachername | 1        |

---------------------------------

  1. schools

----------------------------------

| schoolid |    name    | townid |

----------------------------------

| 1        | schoolname | 1      |

----------------------------------

  1. towns

-----------------

| id |    name  |

-----------------

| 1  | townname |

-----------------

So teacher belongs to school and school belongs to town.

In my Teachers model I have this relation


public function relations()

{

	return array(

		'teacher'=>array(self::BELONGS_TO, 'Schools', 'schoolid'),

	);

}

And in Schools model


public function relations()

{

	return array(

		'school'=>array(self::BELONGS_TO, 'Towns', 'townid'),

	);

}

My question is: how can I get town’s name having teacher’s id (pedid)?

Thank you.

Not really wrong, but your relation names look odd. Shouldn’t it be:




// in Teacher

'school'=>array(self::BELONGS_TO,'Schools','schoolid'),


// In Schools

'town'=>array(self::BELONGS_TO,'Towns','townid');



Then it would work like this:


$teacher=Teacher::model()->with('school.town')->findByPk($someId);

$townname=$teacher->school->town->name;



How can I use it with CGridView?


$this->widget('zii.widgets.grid.CGridView', array(

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

	'columns'=>array(

		array('name'=>'name','header'=>"Ім'я",'value'=>'htmlspecialchars_decode ($data->name,ENT_QUOTES)'),

		array(   

		'name'=>'dn',

		'value'=>'$data->safeDate',

		'header'=>'Дата народження'

		),

		array('name'=>'posada','header'=>"Посада"),

		array('name'=>'school','header'=>"Школа",'value'=>'htmlspecialchars_decode($data->school->name." ".$data->school->stupin." ".$data->school->nomer_sh,ENT_QUOTES)'),

		

		array('name'=>'predmet','header'=>"Предмет"),

		array(    

		'class'=>'CButtonColumn',

                'buttons'=>array(

                                'view'=>array('options'=>array('target'=>'_blank')),

                                 'update'=>array('visible'=>'false'),

                                'delete'=>array('visible'=>'false')

                ), ),),

		));	




What code should be in my search() methods in Teachers and Schools models?

As Mike said try :


..........

'value'=>'$data->->school->town->name',

..........

I get this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘town.’ in ‘on clause’

Check the relation name in School for towns. It must be ‘town’ for the above to work.

It is.

why dont you paste your code(the relations part) … beacause my crystal ball is obviously not working today :)

My relations are shown in the first post and edited in second one by Mike.

Here is my search() method of Teachers model


public function search()

	{

		$criteria=new CDbCriteria;

		$criteria->with='school';

		$criteria->compare('school.rayonid',$this->rayon);

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

		if(!$this->shid)

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

		$criteria->compare('t.schoolid',$this->shid);

		if(!$this->shid)

			$criteria->compare('school.nazva',$this->shkola,true);

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

		.

                .

                .

		$sort = new CSort();

			$sort->attributes = array(

			  'school'=>array(

			    'asc'=>'school.nazva',

			    'desc'=>'school.nazva desc',

			  ),

			  'name',

			  'dn',

			  'posada',

			  'predmet',

			);

		return new CActiveDataProvider('Teachers', array(

			'sort'=>$sort,

			'criteria'=>$criteria,

			'pagination'=>array(

				      'pageSize'=>20,

					),

		));

	}

It provides data for my CGridView (code little above).

What changes have I to make to add the Towns stuff? What search() method must be for Towns?

Are you sure that you did that ? because it looks correct and it should work ?

I changed


$criteria->with='school';

to


$criteria->with='school.town';

in Teachers model and added


array('name'=>'school','value'=>'$data->school->town->name'),

to my CGridWiew and now I get error: Column not found: 1054 Unknown column ‘town.’ in ‘on clause’

Please paste all your relations() definitions. That’s really the only part that matters here. Yii uses an alias with the name of your relation for each related joined table in SQL. So if “town” is not found - something must be wrong with your relations.

I don’t have other definitions than these


// in Teacher

'school'=>array(self::BELONGS_TO,'Schools','schoolid'),


// In Schools

'town'=>array(self::BELONGS_TO,'Towns','townid');

Is it possible that I get error because my teachers table is MyISAM and schools and town InnoDB?

Does this work?


$teacher=Teacher::model()->with('school.town')->findAll();

If it doesn’t: Can you turn on query logging (Set YII_DEBUG to true and configure a CWebLogRoute) and paste the created SQL?


SELECT `t`.`pedid` AS `t0_c0`, `t`.`schoolid` AS

`t0_c1`, `t`.`name` AS `t0_c2`, `t`.`posada` AS `t0_c3`, `t`.`osvita` AS

`t0_c4`, `t`.`nzaklad` AS `t0_c5`, `t`.`predmet` AS `t0_c6`, `t`.`pstazh`

AS `t0_c7`, `t`.`rk` AS `t0_c8`, `t`.`rpa` AS `t0_c9`, `t`.`rpa_p` AS

`t0_c10`, `t`.`katehoriya` AS `t0_c11`, `t`.`zvannya` AS `t0_c12`,

`t`.`stat` AS `t0_c13`, `t`.`dn` AS `t0_c14`, `t`.`dp` AS `t0_c15`,

`t`.`spec` AS `t0_c16`, `t`.`osn` AS `t0_c17`, `t`.`klasy` AS `t0_c18`,

`t`.`kstazh` AS `t0_c19`, `t`.`kurszaklad` AS `t0_c20`, `t`.`rna` AS

`t0_c21`, `t`.`taryf` AS `t0_c22`, `t`.`nagorody` AS `t0_c23`,

`t`.`nagorody_r` AS `t0_c24`, `t`.`nagorody_obl` AS `t0_c25`,

`t`.`nagorody_mon` AS `t0_c26`, `t`.`rkk` AS `t0_c27`, `t`.`rk_p` AS

`t0_c28`, `t`.`problema` AS `t0_c29`, `t`.`data_onov` AS `t0_c30`,

`teacher`.`schoolid` AS `t1_c0`, `teacher`.`nazva` AS `t1_c1`, `teacher`.`stupin` AS

`t1_c2`, `teacher`.`nomer_sh` AS `t1_c3`, `teacher`.`spec` AS `t1_c4`,

`teacher`.`typ` AS `t1_c5`, `teacher`.`tf` AS `t1_c6`, `teacher`.`pidporyad` AS

`t1_c7`, `teacher`.`oporna` AS `t1_c8`, `teacher`.`kvch` AS `t1_c9`,

`teacher`.`rayonid` AS `t1_c10`, `town`.`id` AS `t2_c0`, `town`.`rayon` AS

`t2_c1`, `town`.`pidporyad` AS `t2_c2` FROM `teacher` `t`  LEFT OUTER

JOIN `schools` `teacher` ON (`t`.`schoolid`=`teacher`.`schoolid`)  LEFT OUTER JOIN

`town` `town` ON (`teacher`.`rayonid`=`town`.``)  ORDER BY

name

This part is odd:


... LEFT OUTER JOIN`town` `town` ON (`teacher`.`rayonid`=`town`.``) ...

It’s missing the name of the Pk column of your town table. Not sure, how this can happen, but did you maybe forget to define a primary key in your town table?

And there’s something more wrong in the naming of your relations. See this alias:


LEFT OUTER JOIN `schools` `teacher`

So you obviously still have the wrong name in your Teachers model for the relatoin to table ‘schools’. It should be ‘school’, not ‘teacher’.

Mike you are right I didn’t have primary key in my town table.

Now when I added one it works!!!

Thank you very very much. :)

Actually my real naming is diferent. I designed the names Teachers, Schools and Towns to make my idea more clear.

My real naming is Ppnz for Teachers, Shkoly for Schools and Pidporydkuvannya for Towns.

Here I attach my three models and view ‘vyvid’. You can have a look if you like.