YII2 creating relations in models between tables from 2 databases

Question

I have defined 2 databases , for example

return [
'components' => [
    'db1' => [
        'class' => 'yii\db\Connection',
        'dsn' => 'mysql:host=localhost;dbname=db1name', 
        'username' => 'db1username',
        'password' => 'db1password',
    ],
    'db2' => [
        'class' => 'yii\db\Connection',
        'dsn' => 'mysql:host=localhost;dbname=db2name', 
        'username' => 'db2username',
        'password' => 'db2password',
    ],
],
];

Now i have a table as 'users' in 'db1' and table 'countries' in 'db2'

users 
id , country_code , username , password
1  , DE           , xyz      , 12345
2  , FR           , abc      , 12345

countries
code , name
DE   , Germany
FR   , France
IN   , India

I have defined the foreign key relation between users.country_code & countries.code

ISSUE

But when i try to create the model for 'users' table using gii it gives an error , possibly because the tables relation are from 2 different databases. How to use tables from different databases in relations of a model.

Any suggestions are welcomed

I think gii uses the database connection that is stored in `db`. Thus it creates an error (like [this](http://stackoverflow.com/questions/31915469/multiple-database-connection-yii2-not-working)). — robsch, Apr 05 '16 at 09:09
[This](http://stackoverflow.com/a/27254893/57091) could help. You should overwrite [getDb()](http://www.yiiframework.com/doc-2.0/yii-db-activerecord.html#getDb%28%29-detail) in your model classes. — robsch, Apr 05 '16 at 09:40
Hi robsch , i have already checked the links you provided but if you can let me know how to create a join statement when multiple DBs involved , for tables in a single db it is something like this - 'joinrel'=>array(self::BELONGS_TO,'User','code'), — nshah143, Apr 05 '16 at 10:02
Do you have already any code that you could provide? In SQL the tables need to be prefixed with their database names for real joins. Then it should work. Yii2 would have to realize this. I would have expected that happens if you overwrite getDb(). According to the [guide](http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#cross-database-relations) I thought it would be rather simple... What have you coded so far and which error do you get? Please update your post. — robsch, Apr 05 '16 at 11:41
When want to generate a model using Gii, there is an option which db connection to use. Default is "db" and you should write db1. Leter, when you generate your model using Gii, then you can write relation query in your User model, and that releation will reference your country model. — MrD, Apr 07 '16 at 11:57
Here is a screenshoot http://pokit.org/get/?904453b2b0cdf61173385d1752e432d2.jpg — MrD, Apr 07 '16 at 12:02

Ronilson Cardoso · Answer 1 · 2019-09-12T13:54:21.017

This works in my case to list iten on GridView::widget

-> bd_sisarc is my secound data base
-> deposito_sondagem is a table from my first data base


public static function getDb()   // on your model
{
    return Yii::$app->get('db1');
}

public static function getDb()   // on your model
{
    return Yii::$app->get('db2');
}

public function getEmpresaSondagem()  // Relation on you model
{
    return $this->hasOne(EmpresaSondagem::className(), ['idEmpSondagem' => 'entidade_deposito']);
}





public function search($params)
{


    $this->load($params);

    $sql = "SELECT deposito_sondagem.*
                  FROM
                      deposito_sondagem,
                      `bd_sisarc`.`tbempresasondagem`
                  WHERE
                      `bd_sisarc`.`tbempresasondagem`.`idEmpSondagem`=`deposito_sondagem`.`entidade_deposito`

                      and deposito_sondagem.estado=1
                      and tbempresasondagem.estado=1
                      and numero_registo LIKE '%$this->numero_registo%'
                      and nomeempsondagem LIKE '%$this->nomeEntidade%'
                      and dono_sondagem LIKE '%$this->dono_sondagem%'
                      and data_deposito LIKE '%$this->data_deposito%'";


    $query = DepositoSondagem::findBySql($sql);
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);


    if (!$this->validate()) {
        // uncomment the following line if you do not want to return any records when validation fails
        // $query->where('0=1');
        return $dataProvider;
    }


    return $dataProvider;
}

score -2 · Answer 2 · edited Nov 23 '16 at 13:03

-2

Try this one

SELECT `users`.* FROM `users` LEFT JOIN `db2name`.`countries` ON `users`.`country_code` = `db2name`.`countries`.`code `

edited Nov 23 '16 at 13:03

S.I.

3,250
12
48
77

answered Nov 23 '16 at 12:42

Annie Chandel

197
3
3

He is asking how to create the model and you are telling him how to query the data. – nicolascolman Jul 03 '17 at 19:23

YII2 creating relations in models between tables from 2 databases

2 Answers2

Linked