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,
]);
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');
You can get the result of the first table and the second table separately, then do a join operation in php.
But if the data is huge, it will take a huge amount of time