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). — robsch
– robsch, Commented Apr 5, 2016 at 9:09
This could help. You should overwrite getDb() in your model classes. — robsch
– robsch, Commented Apr 5, 2016 at 9: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
– nshah143, Commented Apr 5, 2016 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 I thought it would be rather simple... What have you coded so far and which error do you get? Please update your post. — robsch
– robsch, Commented Apr 5, 2016 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
– MrD, Commented Apr 7, 2016 at 11:57

Ronilson Cardoso · Accepted Answer · 2019-09-12 13:54:21Z

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

S.I. · Accepted Answer · 2016-11-23 13:03:37Z

-2

Try this one

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

edited Nov 23, 2016 at 13:03

S.I.

3,38112 gold badges53 silver badges87 bronze badges

answered Nov 23, 2016 at 12:42

Annie Chandel

2073 silver badges3 bronze badges

1 Comment

nicolascolman Over a year ago

He is asking how to create the model and you are telling him how to query the data.

Collectives™ on Stack Overflow

YII2 creating relations in models between tables from 2 databases

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related