Yii 1.1: multiactiverecord

Using multiple databases connections in models and gii
20 followers

In need of use multiple databases in my models and after reading the tutorial using mutiple database connections I wrote this code and I'm here sharing with you

It comes with a Gii model generator that accepts multiple databases and will simplify things for you.

Requirements

Tested with Yii 1.1.5, should work in 1.1.*

Usage

To use it put the MultiActiveRecord under your application.components directory or any other imported folder, and the mpgii folder under your extensions folder

Your models must extend "MultiActiveRecord", like:

class myModel extends MultiActiveRecord{}

My mainly need was to find a way to set up a database for certain models, for example, the model "Pages" must connect to "database1" and the model "Sites" must connect to "database2" Using the code here described you can do it this way:

//myModel extends MultiActiveRecord like described above
class Pages extends myModel{
        //overwrite the connection id function that returns by default 'db'
        //the following code is generated automatically with MP-Gii
        function connectionId(){
            return 'db_system';
        }
}

To set up the code to connect to the databases : in your main config file ( usually configs/main.php)

return array(
//to use gii extension component that comes included in the extension do the following
'modules'=>array(
  'gii'=>array(
      'class'=>'system.gii.GiiModule',
      'password'=>'myPassword',
      'generatorPaths' =>array('ext.mpgii'),//this line does the trick
   ),
   //your other modules
),
//...
'components'=>array(
    //your main database connection
   'db'=>array('...'),
    //another database connection
   'db_system'=>array(
        'class'=>'CDbConnection',//!important
        'connectionString'=>'mysql:host=localhost;dbname=db_system',
        'username'=>'myUsername',
        'password'=>'myPassword',
    ),
    //1 more database connection
   'db_user'=>array(
        'class'=>'CDbConnection',//!important
        'connectionString'=>'mysql:host=localhost;dbname=db_user',
        'username'=>'myUsername',
        'password'=>'myPassword',
    ),
    //your other components
),
 
);

The model will use the database connection specified in the method connectionId and declared in your application components

and there you go, now just set up as many databases you want and define it in you models like exemplified above

Here is a screenshot of it: mar gii

hope it helps you

Resources

forum / suport

Total 7 comments

#8427 report it
Maxxer at 2012/06/02 04:58am
cross-db relations

hi. is this supposed to handle multi-db relations? I've model A in db1 and model B in db2, I'm trying to create a CGridView with this relation but like when using the Multiple Database support Yii tries to reach the second table on the same db as the first. Also, in the example above you say

function connectionId()

while it should be

public function connectionId()
#4601 report it
Gustavo at 2011/07/25 11:58am
@Onman

getDbConnection will return always the main db connection, defaults to the db component

this extension overrides getDbConnection, returning the connection defined in connectionId method

#4599 report it
Onman at 2011/07/25 11:45am
How about the getDbConnection() method?

Why not just use the CActiveRecord.getDbConnection() method?

#4220 report it
Gustavo at 2011/06/16 06:38pm
CDbCacheDependency error

Hey dinhtrung Im glad that my extension was useful for you About the problem It works if you set CDbCacheDependency property 'connectionID' to the real one, like

$cacheDep=new CDbCacheDependency('select max(date) from myTable');
$cacheDep->connectionID='MyConnection';

there's no way to do it automatically because it executes the sql directly and does not use AR

Gustavo

#4210 report it
dinhtrung at 2011/06/15 10:59pm
DbCachDependency error

Please note that this will not work for your CDbCacheDependency, cause the query used in Cache is for your main DB, not the Multi AR one.

How can we improve this to support CDbCacheDependency as well?

#2609 report it
Gustavo at 2011/01/23 04:47am
as suggested by @DarkNSF

i changed it to work using the pre-configured database components and also added a couple things to it, and a gii model generator

#2386 report it
DarkNSF at 2010/12/21 10:29am
Hmm

This looks pretty good except I don't think that storing the databases in the params is such a good idea.

Why not just use pre-configured CDbConnections in the Components section of the config?

example:

'components'=>array(
    'user'=>array(
      'class'=>'WebUser',
      'allowAutoLogin'=>true,
    ),
 
    'db'=>array(
      'connectionString' => 'mysql:host=localhost;dbname=db',
      'emulatePrepare' => true,
      'username' => 'db',
      'password' => 'password',
      'charset' => 'utf8',
    ),
 
    'db2'=>array(
      'connectionString' => 'mysql:host=localhost;dbname=db2',
      'emulatePrepare' => true,
      'username' => 'db2',
      'password' => 'password',
      'charset' => 'utf8',
    ),
 
    'db3'=>array(
      'connectionString' => 'mysql:host=localhost;dbname=db3',
      'emulatePrepare' => true,
      'username' => 'db3',
      'password' => 'password',
      'charset' => 'utf8',
    ),
  ),

Leave a comment

Please to leave your comment.

Create extension