viter
(Chekhiv)
November 29, 2010, 9:34am
1
Hello.
Can sombody help me to establish correct relations among three tables?
My tables are
teachers
---------------------------------
| pedid | name | schoolid |
---------------------------------
| 1 |teachername | 1 |
---------------------------------
schools
----------------------------------
| schoolid | name | townid |
----------------------------------
| 1 | schoolname | 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.
mikl
(Mike)
November 29, 2010, 1:58pm
2
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;
viter
(Chekhiv)
November 29, 2010, 2:17pm
3
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?
phreak
(Phreak)
November 29, 2010, 2:56pm
4
As Mike said try :
..........
'value'=>'$data->->school->town->name',
..........
viter
(Chekhiv)
November 29, 2010, 3:01pm
5
I get this error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘town.’ in ‘on clause’
mikl
(Mike)
November 29, 2010, 3:02pm
6
Check the relation name in School for towns. It must be ‘town’ for the above to work.
phreak
(Phreak)
November 29, 2010, 3:14pm
8
why dont you paste your code(the relations part) … beacause my crystal ball is obviously not working today
viter
(Chekhiv)
November 29, 2010, 3:19pm
9
My relations are shown in the first post and edited in second one by Mike.
viter
(Chekhiv)
November 29, 2010, 3:36pm
10
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?
phreak
(Phreak)
November 29, 2010, 3:42pm
11
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;
Are you sure that you did that ? because it looks correct and it should work ?
viter
(Chekhiv)
November 29, 2010, 3:49pm
12
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’
mikl
(Mike)
November 30, 2010, 8:09am
13
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.
viter
(Chekhiv)
November 30, 2010, 8:37am
14
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?
mikl
(Mike)
November 30, 2010, 8:40am
15
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?
viter
(Chekhiv)
November 30, 2010, 9:34am
16
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
mikl
(Mike)
November 30, 2010, 9:42am
17
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?
mikl
(Mike)
November 30, 2010, 9:46am
18
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’.
viter
(Chekhiv)
November 30, 2010, 9:49am
19
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.
viter
(Chekhiv)
November 30, 2010, 10:10am
20
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’.
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.