Yii2: Is it possible 2 databases query at once with JOIN?

Question

I have project on Yii2. I have 2 databases. I need to execute command with join like

SELECT * FROM `table1` LEFT JOIN `table2` ON `table1`.`id` = `table2`.`id`;

..where table1 is from db1 and table2 from db2. notice: db2 is on another 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 to do that on clean mysql/php? on Yii2? .. or the only way is to fetch results from table1 and table2 separated and then loop to compare id?

Q2: How to compare id in yii2 in dataProvider?

$query = Table1::find();
$query2 = Table2::find();
// how compare id?

$dataProvider = new ActiveDataProvider([
    'query' => $query,
]);

possible duplicate of [MySQL -- Joins Between Databases On Different Servers Using Python?](http://stackoverflow.com/questions/5832787/mysql-joins-between-databases-on-different-servers-using-python) Ignore the python part - this is a mysql problem. — Tim G, Apr 27 '15 at 17:41
It is a yii2 problem because of the way active record works, by default, yii2 uses a connection which specifies a database. To use relations between two databases, you have to specify the db in the model table defintion. — Jairus, Sep 29 '20 at 03:47

score 2 · Answer 1 · answered May 05 '15 at 21:45

It's not possible to perform a JOIN between two different databases in MySQL. However, Yii's ActiveRecord relation system doesn't use JOINs to retrieve related data, but a separate "IN" query, which allows it to retrieve relational data across different databases or even different DBMS types.

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

It is possible, but requires specifying the schema with the table name in the model assuming both databases are on the same server. The other option is federated tables. — Jairus, Jun 30 '20 at 13:04

score 0 · Answer 2 · answered Feb 12 '16 at 19:15

0

You can fetch result of first table and second table separately then perform the join operation in php.

But if the data is huge it will take huge amount of time

answered Feb 12 '16 at 19:15

Rasaiya

11
2

Jairus · Answer 3 · 2021-03-14T15:43:06.167

Using Federated Table

MySQL Example

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=utf8mb4;

See Creating a FEDERATED Table

The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.

Same Connection w/ different Schema

db_conn---> db1 -> tableA
        |
        |-> db2 -> tableB

If both databases are on the same db connection instance, then this is easily done if you specify the schema with the table name like so:

Class TableA extends \yii\db\ActiveRecord
{
    ...
    public static function tableName()
    {
        return 'databaseA.tableA';
    }
    ....
}

Class TableB extends \yii\db\ActiveRecord
{
    ...
    public static function tableName()
    {
        return 'databaseB.tableB';
    }
    ....
}

Now you can do sub-query as well:

$subQuery = TableB::find()
    ->select('id')
    ->where(['column' => 'criteria'])
    ->column();

$query = TableA::find()
    ->join(['b' => $subQuery], 'b.column_pk = a.column_fk')
    ->all();

score 0 · Answer 4 · answered Nov 27 '20 at 07:30

0

You can create view for the table1 in db2 and take the join with that view. but as you have mentioned that db2 is on another server then taking the join will be costly operation.

answered Nov 27 '20 at 07:30

Sagar Kelkar

1
1

Yii2: Is it possible 2 databases query at once with JOIN?

4 Answers4

Using Federated Table

Same Connection w/ different Schema