Yii2: Is it possible to make two database queries at once using JOIN?
I have a Yii2 project. I have 2 databases. I need to execute a command with join
like
SELECT * FROM `table1` LEFT JOIN `table2` ON `table1`.`id` = `table2`.`id`;
.. where table1
- from db1
and table2
from db2
. notification: db2
resides on a different server.
'db1' => [
'class' => 'yii\db\Connection',
'dsn' => 'mysql:host=localhost;dbname=db1',
'username' => '...',
'password' => '...',
'charset' => 'utf8',
],
'db2' => [
'class' => 'yii\db\Connection',
'dsn' => 'mysql:host=anotherserver.com;dbname=db2',
'username' => '...',
'password' => '...',
'charset' => 'utf8',
]
Q1: How can I do this in pure mysql / php? on Yii2? .. or the only way is to get the results from table1
and table2
separated and then loop for comparison id
?
Q2: How to compare id
in yii2 dataProvider
?
$query = Table1::find();
$query2 = Table2::find();
// how compare id?
$dataProvider = new ActiveDataProvider([
'query' => $query,
]);
source to share
It is not possible to do JOIN between two different databases in MySQL. However, the Yii ActiveRecord relationship system does not use JOINs to retrieve related data, but is a separate "IN" query that allows you to retrieve relational data across different databases or even different types of DBMSs.
class Table1 extends ActiveRecord {
public static function getDb()
{
return Yii::$app->db1;
}
public function getTable2()
{
return $this->hasMany(Table2::class, ['id' => 'id']);
}
}
class Table2 extends ActiveRecord {
public static function getDb()
{
return Yii::$app->db2;
}
}
$query = Table1::find()->with('table2');
source to share