Multi Data Provider

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.

1 0
4 followers
Viewed: 10 725 times
Version: 1.1
Category: Tips
Written by: Sharon Lavie
Last updated by: Sharon Lavie
Created on: Oct 4, 2013
Last updated: 2 years ago
Update Article

Revisions

View all history

Related Articles