Yii2. Working with multiple databases

Adding connection settings to several databases in Yii2

To work with several databases in Yii2, you need to add all new connections to the file config/web.php:

<?php

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

'myNewDB' => [
    'class' => 'yii\db\Connection',
    'dsn' => 'mysql:host=127.0.0.1;dbname=my_new_database',
    'username' => 'user',
    'password' => 'password',
    'charset' => 'utf8',
],

You can also use a separate file to connect a new database, this is done as follows:

<?php

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

Do not renamed the property "db" of the component, as this will result in an error.

Specify the appropriate database for use in the Yii2

To use a new database in the model, you need to override the default "db" configuration, doing this as follows:

<?php

class MyModel extends \yii\db\ActiveRecord {

    //...

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

    //...

}

The getDb function sets up the database connections for the model.

The joinWith() operation for different databases in Yii2

And so, when both tables are in the same database, for example: Author (model\app\models\Author.php) and Post (model\app\models\ Post.php) we do this:

$authorsPosts = \app\models\Author::find()
    ->joinWith('post')
    ->where(['post.visibility' => \app\models\Post::STATUS_VISIBILITY])
    ->all();

Such a relation for tables residing in different databases will look like this:

$authorsPosts = \app\models\Author::find()
    ->joinWith(['order' => function($query) { 
        return $query->from('myNewDB.' . \app\models\Post::tableName())
            ->andWhere(['post.visibility' => \app\models\Post::STATUS_VISIBILITY]); 
        }])
    ->all();

In this case, the Post table from the myNewDB database (model:\app\models\Order.php) uses a method to configure the connection to the getDB() database.

And finally, use a connection to several databases in one application with the mind, you do not need to make different bases for each table, but in turn you do not need to push many tables into the same database (especially for a cardinal difference in the application area).