Search related data in different data base

Question

I use yii2 to build one app which need to connect some tables. I can join them simply and search for data in related fields. I am doing it by adding the connection like this>

public function getNextTab()
{
return  $this->hasOne(NextTab::className(),['id' =>'id_nexttab']);  
}

and ask for the data in search model using like this ->

->where ('id'='ok') ->
->joinWith('nextTab')
->joinWith('nextTab.nextTab1')
->joinWith('nextTab.nextTab1.nextTab2');

My problem is when I try to do this with tables from different database. The query is give me error like

SQLSTATE[42S02]: Base table or view not found:

any tips how to pass it? or how to do other way of connection to have the data.

score 1 · Accepted Answer · edited May 23 '17 at 12:22

1

Joining tables from different databases may not be supported by your RDBMS (PostgreSQL for example). But if supported (MSSQL, MySQL) then table names should be prefixed with database name (and schema if needed). You can achieve this in Yii2 using {{%TableName}} syntax in tableName() function.

public static function tableName()
{
    return '{{%table_name}}';
}

But be careful with joining tables from different databases if they are located on different servers -- this can be very slow.

If you just want to get related data (joined tables are not used in WHERE) then use with() instead of joinWith(). This will be executed as separate query with IN statement. In most cases this way has a better performance and no problems with different sources (and even different DBMS).

->with('nextTab', 'nextTab.nextTab1', 'nextTab.nextTab1.nextTab2')

edited May 23 '17 at 12:22

Community

1
1

answered Jul 28 '16 at 11:09

oakymax

1,454
1
14
21

tx. It works now. with connections. I just added to each model function 'public static function tableName() { return 'db1.Tab'; }' and 'public static function tableName() { return 'db2.Tab2'; }' for next tables and it works. – Alexei Aug 01 '16 at 10:08
@Alexei good. Glad to help. Please, update the answer with your final code, and mark as solved. This may help someone else. And don't forget to upvote ;-) – oakymax Aug 01 '16 at 11:16

score 0 · Answer 2 · answered Jul 27 '16 at 16:27

0

Configure your second database component in the application's config.
Override the getDB() function in your ActiveRecord Model to return the second DB component.

This will attach your Model to the secondary DB and allow you to query from the table in secondary DB.

Config sample:

'components' => [
    'db2' => [
        'class' => 'yii\db\Connection',
        'dsn' => 'mysql:host=localhost;dbname=db2',
        'username' => 'hello',
        'password' => 'world',
    ],
],

getDB() function override sample:

class Tab extends ActiveRecord
{
    public static function getDb()
    {
        // use the "db2" component
        return \Yii::$app->db2;  
    }
}

Good Luck!

answered Jul 27 '16 at 16:27

Abdul Mueid

31
3

@Abdula I had the connection like this because in my project I use both of DB for different parts, but when I try to replaced 'getBD()' i get different error: 'The table does not exist:Tab'. So in this way I just dont have anymore connection with first main model. Is it something what I am missing ? – Alexei Jul 28 '16 at 09:29
`Tab` was an example. Your actual class needs to be based on your schema. In order to better understand your scenario, I would need to see your schema. Can you share that? – Abdul Mueid Jul 28 '16 at 13:00

Search related data in different data base

2 Answers2