I am trying to query multiple databases (same table, same structure, just different databases) in a model file. I am using the "mpgii" extension to create the models. Then I override the connectionId() function like this:
public function connectionId()
{
return array('db_1','db_2');
}
I know I can’t pass arrays to the MultiActiveRecord class so I changed the getDbConnection function to check if the returned argument is an array and if it is, get an instance for each connection like this:
$dbNameArray=$this->connectionId();
if (is_array($dbNameArray)) {
foreach ($dbNameArray as $dbName) {
//var_dump($dbName);
if(!isset(self::$db[$dbName])){
if(Yii::app()->hasComponent($dbName) && (self::$db[$dbName]=Yii::app()->getComponent($dbName)) instanceof CDbConnection){
self::$db[$dbName]->setActive(true);
}else
throw new CDbException(Yii::t('yii','Active Record requires a "'.$dbName.'" CDbConnection application component.'));
}
}
} else {
$dbName=$this->connectionId();
//var_dump($dbName);
if(!isset(self::$db[$dbName])){
if(Yii::app()->hasComponent($dbName) && (self::$db[$dbName]=Yii::app()->getComponent($dbName)) instanceof CDbConnection){
self::$db[$dbName]->setActive(true);
}else
throw new CDbException(Yii::t('yii','Active Record requires a "'.$dbName.'" CDbConnection application component.'));
}
return self::$db[$dbName];
}
But when I try to access the page using the model I get this error:
Fatal error: Call to a member function getSchema() on a non-object in C:\xampp\htdocs\yii\framework\db\ar\CActiveRecord.php on line 2309
may be this scenario is not suitable for using the AR . try DAO .
or just use one “db” component to query partial result set , then switch to another “db1” compoent fetch another partial row set , then make a merging !
The second method that you suggested seems easier to me so how do I switch from one component to the other? As I said I can only return ONE component from connectionID
$oldDb = Yii::app()->db;
//switch to anther db:
Yii::app()->setComponent('db',Yii::app()->db2);
// then use your ar to do the stuff !
....
// after query , switch back :
Yii::app()->setComponent('db',$oldDb);
i have not met such things so the code just my thought , try it yourself !
Several days have passed since I fixed this so I thought I would post my solution:
Define multiple db components in config/main.php
In the model create a random public static variable like $dbName and a static array who will contain the databases that the model will search in
Modify the search function to use CArrayDataProvider by merging the returned model arrays like this:
// Get data from all databases
foreach (self::$dbList as $dbItem) {
self::$dbName = $dbItem;
$$dbItem = Order::model()->findAll($criteria);
}
// NOTE: To add more databases add a variable in array_merge with the name of the db component e.g."db_3"
$data = new CArrayDataProvider(array_merge($db_1, $db_2), array(
'keyField'=>'order_id',
'sort'=> array(
'attributes'=> array(
'order_id'=>array(
'DESC'=>'order_id DESC',
),
'date_modified'=>array(
'ASC'=>'date_modified DESC'
),
'*',
),
'defaultOrder'=>'order_id DESC'
),
'pagination'=> array(
'pageSize'=>10,
),
));
Modify the connectionId() function like this:
public function connectionId()
{
if (self::$dbName === null) {
return self::$defaultDb;
} else {
return self::$dbName;
}
}
Finally modify controller functions to redirect you to appropriate db by changing the public static variable of the model.
e.g.:
public function actionView($id,$store)
{
if ($store==='blah1') {
Order::$dbName='db_1'; // This is the one you care about
} elseif ($store==='blah2') {
Order::$dbName='db_2';
}
$this->render('view',array(
'model'=>$this->loadModel($id),
));
}
(In my case I check a model attribute that is unique to each db)
That’s it. It doesn’t look very clean to me but it does the trick.
