Yii2 ActiveRecord How to JOIN tables between two mysql DB?

Question

Yii

There are 2 models KladrCity and Area, each of them is in different DB both MYSQL.
In KladrCity

public static function getDb() {
        return \Yii::$app->dbKladr;
}

In Area

public function getCity()
{
        return $this->hasOne(KladrCity::className(), ['code' => 'city_id']);
}

With JOIN

$models = \common\models\Area::find()->joinWith('city');

The error falls out that there is no city table in the DB.
That is, when JOIN activerecord builds a query without regard to the database. As a result, the query is built into the database in which the table to which the JOIN occurs is located.
So the question arises how to JOIN two tables from different databases in YII2.

Answer 1

JOINworks inside your DB, it cannot make it to another database. In such cases, 2 requests are made, we select all the necessary areas - in php, as an example, we select through array_unique - only unique ones city_id, we make a request to the database with city where in(city ids) - we assign the array key to be city_id, at any time we get the city for the area by type like this:$city[$area->city_id]

Answer 2

ActiveRecordYii doesn't do this on purpose, because it breaks compatibility with other types of databases where similar ones JOINdon't work.
Developers advise to use with().

Extended usage from documentation:

// find customers and bring back together their country and active orders
// SELECT * FROM `customer`
// SELECT * FROM `country` WHERE `id` IN (...)
// SELECT * FROM `order` WHERE `customer_id` IN (...) AND `status` = 1
$customers = Customer::find()->with([
    'country',
    'orders' => function ($query) {
        $query->andWhere(['status' => Order::STATUS_ACTIVE]);
    },
])->all();