Multiple database connections and Yii 2.0

Question

I have two databases, and every database has the same table with the same fields, but how do I get all records from all of two databases at the same time in Yii 2.0?

Answer 1

First you need to configure your databases like below:

return [
'components' => [
    'db1' => [
        'class' => 'yii\db\Connection',
        'dsn' => 'mysql:host=localhost;dbname=db1name', //maybe other dbms such as psql,...
        'username' => 'db1username',
        'password' => 'db1password',
    ],
    'db2' => [
        'class' => 'yii\db\Connection',
        'dsn' => 'mysql:host=localhost;dbname=db2name', // Maybe other DBMS such as psql (PostgreSQL),...
        'username' => 'db2username',
        'password' => 'db2password',
    ],
],
];

Then you can simply:

// To get from db1
Yii::$app->db1->createCommand((new \yii\db\Query)->select('*')->from('tbl_name'))->queryAll()

// To get from db2
Yii::$app->db2->createCommand((new \yii\db\Query)->select('*')->from('tbl_name'))->queryAll()

If you are using an active record model, in your model you can define:

public static function getDb() {
    return Yii::$app->db1;
}

//Or db2
public static function getDb() {
    return Yii::$app->db2;
}

Then:

If you have set db1 in the getDb() method, the result will be fetched from db1 and so on.

ModelName::find()->select('*')->all();

Answer 2

Just to add: I followed the answer provided but still got an error: "Unknown component ID: db"

After some testing, here is what I discovered: The function getDB is only called AFTER a connection is made to db. Therefore, you cannot delete or rename 'db' in the config file. Instead, you need to let the call to 'db' proceed as normal and then override it afterwards.

The solution (for me) was as follows:

In config/web.php add your second database configuration below db as follows:

'db' => require(__DIR__ . '/db.php'),
'db2' => [
    'class' => 'yii\db\Connection',
    'dsn' => 'mysql:host=localhost;dbname=name',
    'username' => 'user',
    'password' => 'password',
    'charset' => 'utf8',
    'on afterOpen' => function ($event) {
        $event->sender->createCommand("SET time_zone = '+00:00'")->execute();
    },
],

DO NOT rename db. Failure to find db will cause an error. You can name db2 whatever you like.

Now in the model, add the following code:

class ModelNameHere extends \yii\db\ActiveRecord {

   // add the function below:
   public static function getDb() {
       return Yii::$app->get('db2'); // second database
   }

This will now override the default db configuration.

I hope that helps somebody else.

Note: you can include the configuration for db2 in another file but you cannot include it in the db.php file (obviously). Instead, create a file called db2.php and call it as you do db:

'db' => require(__DIR__ . '/db.php'),    
'db2' => require(__DIR__ . '/db2.php'),

Thanks

Answer 3

Our situation is a little more complex, we have a "parent" database which has a table that contains the name of one or more "child" databases. The reason for this is that the Yii project is instantiated for each of our clients, and the number of child databases depends on the client, also the database names are arbitrary (although following a pattern).

So we override \yii\db\ActiveRecord as follows:

class LodgeActiveRecord extends \yii\db\ActiveRecord
{

public static function getDb()
{
    $lodgedb = Yii::$app->params['lodgedb'];
    if(array_key_exists( $lodgedb, Yii::$app->params['dbs'])) {
        return Yii::$app->params['dbs'][ $lodgedb ];
    }
    $connection = new \yii\db\Connection([
        'dsn' => 'mysql:host=localhost;dbname=' . $lodgedb,
        'username' => Yii::$app->params['dbuser'],
        'password' => Yii::$app->params['dbpasswd'],
        'charset' => 'utf8',
    ]);
    $connection->open(); // not sure if this is necessary at this point
    Yii::$app->params['dbs'][ $lodgedb ] = $connection;
    return $connection;
}

}

Before calling any database function, first set Yii::$app->params['lodgedb'] to the name of the database required:

Yii::$app->params['lodgedb'] = $lodge->dbname; // used by LodgeActiveRecord

Your model classes don't change except they extend from LodgeActiveRecord:

class BookingRooms extends \app\models\LodgeActiveRecord

Answer 4

If you're using schmunk42/yii2-giiant to generate model classes, there is a 'modelDb' property which you can set to use a database component other than 'db'.