CODEWITHSUNDEEP
sqldatabaseyii2
Alexei
Alexei

Reputation: 119

Search related data in different data base

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.

Upvotes: 2

Views: 481

Answers (2)

oakymax
oakymax

Reputation: 1474

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')

Upvotes: 1

Abdul Mueid
Abdul Mueid

Reputation: 31

  1. Configure your second database component in the application's config.
  2. 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!

Upvotes: 0

Related Questions