CODEWITHSUNDEEP
phpmysqlsqljoinyii2
sirjay
sirjay

Reputation: 1766

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

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,
]);

Upvotes: 3

Views: 4846

Answers (4)

Jairus
Jairus

Reputation: 846

Using Federated Table

MySQL Example

Using Federated Engine

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

Upvotes: 0

Sagar Kelkar
Sagar Kelkar

Reputation: 3

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.

Upvotes: 0

Rasaiya
Rasaiya

Reputation: 11

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

Upvotes: 0

laszlovl
laszlovl

Reputation: 491

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

Upvotes: 2

Related Questions