There is a main connection to the project_database database. But besides it, there are also n- number of databases for some users of the system. Connection parameters to user databases are stored in the main database in the user table. You must connect to the user database project_database_client_123 and execute the query.

Need to create a component for each connection? And then override the getDb method of the Application to switch between them. Or is there some more elegant way?

class Connection extends \yii\db\Connection { /** * @var \yii\db\Connection[] $connections */ private $connections = []; public function getClientConnection($clientID) { if (!isset($this->connections[$clientID])) { if (!$client = Client::findOne($clientID)) { throw new Exception('Client not found'); } $connection = new \yii\db\Connection([ 'dsn' => "{$this->dsn}_client_{$clientID}", 'username' => $client->db_user, 'password' => $client->db_password, ]); $connection->open(); $this->connections[$clientID] = $connection; } return $this->connections[$clientID]; } public function closeClientConnection($clientID) { if (isset($this->connections[$clientID])) { $this->connections[$clientID]->close(); unset($this->connections[$clientID]); } } }

Well, use somewhere like that.

 $results = Yii::$app->db ->getClientConnection($account->client_id) ->createCommand($query, [ ':accountID' => $accountID, ]) ->queryAll();

PS These are exactly different databases, not schemas of one database.

    1 answer 1

    If you use ActiveRecord, and to select a database, you take information about the current authorized user, it will be convenient and quite elegant to override getDb not in Application but specifically in those AR classes whose tables are "shuffled" in different databases.

    For example, something like this (for the basic template):

    components / DbConnectionManager.php

     class DbConnectionManager extends Component { public function getClientConnection($clientID) { // your code here } public function getCurrentClientConnection() { retrun $this->getClientConnection(\Yii::$app->user->id); } }

    config / web.php

     'components' => [ 'dbConnectionManager' => [ 'class' => 'app/components/DbConnectionManager' ] ]

    models / SomeUserData.php

     class SomeUserData extends ActiveRecord { public static function getDb() { return \Yii::$app->dbConnectionManager->getCurrentClientConnection(); } }
    • In general, the solution with the redefinition of getDb for the model is interesting. Thank you, take note. But alas, this is the case when ActiveRecord is not used. Yes, and specifically in this situation. It turns out that the Client model itself and its connections belong to the main base, and detailed data on clients are stored in shards, and one model will work simultaneously with several connections. So far, he has settled on a solution with the expansion of the base component - Ninazu
    • one
      @Ninazu is understandable. But if we talk about "elegance", then I would recommend to carefully isolate the logic of sharding in a separate class. For example, ClientConnection extends \yii\db\Connection and (new ClientConnection($client))->createCommand ... Well, or something else. In any case, the way you have written (Connection generates a Connection that is not semantically related to the parent + procedural approach for opening / closing) is not very good - not transparent, it will be difficult to respond to unit testing, etc ... source of errors in general. see "SOLID Principles". - oakymax