Multi Data Provider

You are viewing revision #4 of this wiki article.
This version may not be up to date with the latest version.
You may want to view the differences to the latest version.

next (#5) »

Lately, i needed to deal with the following scenario: read from two databases (db1,db2), and write to a third database (db3).

I searched yii forum and wiki for a solution, and when i haven't found one, i wrote a simple solution that fitted my needs, and i hope that it may help someone else.

the solution was changing 3 simple things - add db1 and db2 to my protected/config/main.php

'components'=>array (
'db1'=>array(
			'connectionString' => 'mysql:host=localhost;dbname=db1',
                        'class'=>'CDbConnection',
			'emulatePrepare' => true,
			'username' => 'root',
			'password' => '',
			'charset' => 'utf8',
		),

'db2'=>array(
			'connectionString' => 'mysql:host=localhost;dbname=db2',
                        'class'=>'CDbConnection',
			'emulatePrepare' => true,
			'username' => 'root',
			'password' => '',
			'charset' => 'utf8',
		),

)

add a simple class that extends the CArrayDataProvider that i named MultiDataProvider: (i put it in /protected/components directory)

class MultiDataProvider extends CArrayDataProvider
    {
        
        public function __construct($dbArray = array(),$sql, $config = array()) {
            
            //get an array of CArrayDataProvider
            $dataProviders = $this->createSqlDataProviders($dbArray,$sql);
            
            $this->rawData = $this->createOneArray($dataProviders);
            
        }
        
        //create merged array from many CSqlDataProviders
        private function createOneArray($dataProviders)
        {
            $mergedArray = array();
            
            foreach($dataProviders as $dataProvider)
            {
                $data = $dataProvider->sql;
                for ($i=0;$i<count($dataProvider->sql);$i++)
                {
                    $myData = $data[$i];
                    array_push($mergedArray, $myData);
                }
            }
            
            return $mergedArray;
        }
        
        //create an array of many CSqlDataProviders (one for each db)
        private function createSqlDataProviders($dbArray,$sql)
        {
            $dataProviders = array();
            foreach ($dbArray as $db)
            {
                $sqlCommand=Yii::app()->$db->createCommand($sql)->queryAll();
                $dataProvider = new CSqlDataProvider($sqlCommand);
                
                array_push($dataProviders, $dataProvider);
            }
            
            return $dataProviders;
            
            
        }
        
    }

the controller index action:

public function actionIndex()
	{
                
                $sql = "SELECT * FROM tbl_person";
                $dbArray = array('db1','db2');
                
                $multiDataProvider = new MultiDataProvider($dbArray,$sql,array(
                            'id' => 'person',
                        ));
		
		$this->render('index',array(
                        'dataProvider'=>$multiDataProvider,
		));
	}

limitations: this data provider extension works only for widgets that accept CArrayDataProvider, like CGridView.

Note: if your databases (db1, db2) id aren't unique, meaning that table's uid can reapet it self on both databases, you should add a db id to the MultiDataProvider class.

BTW: db3, that i have mentioned above, will be the main db of my project, so the model is linked to that db connection, and in my config main, it is defined as 'db'.

i'll be glad to get your corrections and notices.