I’m about to embark on an application that will use models that reference different databases. If anyone else has some suggestions on how to do this cleanly in Yii I would be very appreciative. Also, I think it is a topic that should be covered in the Yii documentation.
To clarify the data model/database relationship:
model: alpha => database: db1, table: alpha
model: beta => database: db2, table: beta
Read this page to configure a database connection.
The default database component is ‘db’, but you can configure different databases by copying that code to a component named ‘db2’ (which may hold your beta table).
in your beta model override the getDbConnection() method to return the ‘db2’ component.
Also if your databases are on the same machine you could override the tableName function to return database.tableName instead of just tableName
Config:
'components'=>array(
//…
'db'=>array(
'class'=>'system.db.CDbConnection',
'connectionString'=>'mysql:host=localhost;dbname=db1',
'username'=>'root',
'password'=>'',
'charset'=>'utf8',
),
'db2'=>array(
'class'=>'system.db.CDbConnection',
'connectionString'=>'mysql:host=localhost;dbname=db2',
'username'=>'root',
'password'=>'',
'charset'=>'utf8',
),
//…
),
SQL:
/* Post table */
DROP TABLE IF EXISTS `post`;
CREATE TABLE IF NOT EXISTS `post` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`text` TEXT NOT NULL,
PRIMARY KEY (`id`)
);
/* Comment table */
DROP TABLE IF EXISTS `comment`;
CREATE TABLE IF NOT EXISTS `comment` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`text` TEXT NOT NULL,
`postId` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
Post model is a standard one. Comment model looks like this:
class Comment extends CActiveRecord {
//…
public function getDbConnection(){
return Yii::app()->db2;
}
public function tableName(){
return 'db2.comment';
}
//…
}
Then you can do:
Post::model()->with('comments')->findAll();