Yii 2 - How to set connection in DB Query for multi database

Question

How to set db connection in yii-db-query?

I have a 3 database connection:

db, db2 and db3

When I used this default Query:

$query = (new \yii\db\Query())
            ->select('*')
            ->from('trans_journal')
            ->all();

It will return an error. The trans_journal is not found, because the trans_journal is from the db2 connection.

And when I used this Query:

$query = (new \yii\db\Query())
            ->select('*')
            ->from('trans_journal')
            ->all(\Yii::$app->db2);

The query will be successfully generated but the problem is it will return an array.

Is there another way to solved this?

Jonnny · Accepted Answer · 2018-09-13 18:45:24Z

if you are using an active record model, in your model you can redefine properly the getDB function for each model:

  // Model1 

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

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

If you are using command you can set the db in use just in createCommand call

// To get from db1
Yii::$app->db1->createCommand(
          (new \yii\db\Query)->select('col1, col2, ... ')->
                          from('your_table_db1'))->queryAll();

// To get from db2
Yii::$app->db2->createCommand(
            (new \yii\db\Query)->select('col1, col2, ... ')->
                          from('your_table_db2')->queryAll();

The all() method executes the query and returns all results as an array.

You can iterate over the result for get each model eg:

 $results =  Yii::$app->db1->createCommand(
          (new \yii\db\Query)->select('col1, col2, ... ')->
                          from('your_table_db1'))->queryAll();

foreach($results as $key => $value ){
    echo $value->col1;
    // or $value['col1];

}

user210195 · Accepted Answer · 2018-08-02 08:05:52Z

2

$query = (new \yii\db\Query())
 ->select('*')
 ->from('trans_journal')
 ->all();

 $dataProvider = new ActiveDataProvider([
        'db' => Yii::$app->get('db2'),
        'query' => $query,
    ]);

$models = $dataProvider->getModels();

answered Aug 2, 2018 at 8:05

user210195

611 bronze badge

Collectives™ on Stack Overflow

Yii 2 - How to set connection in DB Query for multi database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related