Say the schema is as follows:
CREATE TABLE table1 (
id INT NOT NULL,
-- ...,
PRIMARY KEY (id)
) ENGINE = InnoDB;
CREATE TABLE table2 (
table1_id INT NOT NULL,
-- ...,
PRIMARY KEY (table1_id),
CONSTRAINT fk_table2_table1
FOREIGN KEY (table1_id) REFERENCES table1 (id)
ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB;
CREATE TABLE table3 (
table1_id INT NOT NULL,
t3col INT,
-- ...,
PRIMARY KEY (table1_id),
CONSTRAINT fk_table3_table1
FOREIGN KEY (table1_id) REFERENCES table1 (id)
ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB;
The table2→table1 and table3→table1 relations are both identifying relations, hence the PK of each table is also the FK to table1.
Gii produces:
class Table1 extends CActiveRecord {
public function relations() {
return array(
'table2' => array(self::HAS_ONE, 'Table2', 'table1_id'),
'table3' => array(self::HAS_ONE, 'Table3', 'table1_id'),
);
}
...
}
class Table2 extends CActiveRecord {
public function relations() {
return array(
'table1' => array(self::BELONGS_TO, 'Table1', 'table1_id'),
);
}
...
}
class Table3 extends CActiveRecord {
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(
'table1' => array(self::BELONGS_TO, 'Table1', 'table1_id'),
);
}
...
}
Which is great, as far as it goes. But clearly, with that schema one can also do:
SELECT * FROM table2
LEFT JOIN table3 ON table3.table1_id=table2.table1_id;
And in Yii I would like to be able to do:
$m2 = Table2::model()->findbyPk($id);
$col = $m2->table3r->t3col; // 'table3r' being the name of the table2→table3 relation
But I cannot figure out how to write the ‘table3r’ relation for the Table2 model. The variants I’ve tried produce rather surprising error messages:
'table3r' => array(self::HAS_ONE, 'Table3', 'table1_id')
'table3r' => array(self::HAS_ONE, 'Table3', 'table1_id,table1_id')
// both of these throw: CException: Property "Table2.id" is not defined.
'table3r' => array(self::BELONGS_TO, 'Table3', 'table1_id')
'table3r' => array(self::BELONGS_TO, 'Table3', 'table1_id,table1_id')
// both throw: CDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'table3r.id' in 'where clause'
The first is odd because there never was any suggestion that model Table1 had a property ‘id’. The second because there never was a table named table3r.
How can one properly define this relation?
Attached is a yiic generated webapp that lets you play with the relation definitions. There are fixtures and a unit test. The schema is in protected/data.