Good Morning,
I’m having problems to join data from Different Tables of Different databases of Different DBMSs.
I currently have data in two databases of two different DBMSs: one MySQL, the other SQL server. I have the relationships made in the models and I can use them normally if I use them separately, but when I try to join them, an error occurs because it tries to join in MySQL using a SQL server table.
Is there any way I can join between different DBMS tables?
Example of what I’m trying to do:
class UserCarrier extends \yii\db\ActiveRecord
{
//Database: MySQL
public static function tableName()
{
return 'user_carrier';
}
/**
* @return \yii\db\ActiveQuery
*/
public function getCarrier() {
return $this->hasOne(Carrier::className(), ['A4_COD' => 'carrier_id']);
}
}
class Carrier extends \yii\db\ActiveRecord
{
//Database: MS SQL server
public static function tableName()
{
return 'SA4010';
}
}
And the join I am trying to do (inside the function search() from the UsercarrierSearch model):
class UsercarrierSearch extends Usercarrier
{
public function search($params)
{
$query = Usercarrier::find();
$query->joinWith('carrier');
}
}
The error i’m having:
Database Exception – yii\db\Exception
SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘azulejo2.sa4010’ doesn’t exist
The SQL being executed was: SELECT COUNT(*) FROM user_carrier LEFT JOIN user ON user_carrier.user_id = user.id LEFT JOIN SA4010 ON user_carrier.carrier_id = SA4010.A4_COD
Error Info: Array
(
[0] => 42S02
[1] => 1146
[2] => Table 'azulejo2.sa4010' doesn't exist
)
↵
Caused by: PDOException
SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘azulejo2.sa4010’ doesn’t exist
in C:\Users\pedro.frota\www\azulejo2\vendor\yiisoft\yii2\db\Command.php at line 902
Note: ‘azulejo2’ is the name of the database that is in MySQL
Thanks!