CODEWITHSUNDEEP
yii2
Gemark Almacen
Gemark Almacen

Reputation: 340

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

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?

Upvotes: 3

Views: 4647

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133400

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];

}

Upvotes: 5

user210195
user210195

Reputation: 61

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

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

$models = $dataProvider->getModels();

Upvotes: 2

Related Questions