joinWith + criteria over multiple databases not working

[stefandoorn]

Introduction
First of all; I'm not sure if this is not working by design or that it's a bug.

What I tried is making a join over two tables in separate databases with a WHERE statement on it.

The use case is that we have a second database with log tables and in the backend would like to join this with live data. I made a simplified test case.

I tried searching the forum + issues but couldn't find a similar issue.

Environment

• PHP 5.4.3
• Yii 2.0.0-dev
• MySQL 5

Reproduce

Create two database configs in /config/web.php (with according settings):

        'db' => require(__DIR__ . '/db.php'),
        'db2' => require(__DIR__ . '/db2.php')

Create database setup for database #1:

CREATE TABLE IF NOT EXISTS `shop` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `shop` (`id`, `name`) VALUES
    (1, 'Shop #1'),
    (2, 'Shop #2');

Then create setup for database #2:

CREATE TABLE IF NOT EXISTS `employee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `shop_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK__yii2_test1.shop` (`shop_id`),
  CONSTRAINT `FK__yii2_test1.shop` FOREIGN KEY (`shop_id`) REFERENCES `yii2_test1`.`shop` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `employee` (`id`, `name`, `shop_id`) VALUES
    (1, 'Jon Doe', 1),
    (2, 'Abraham Lincoln', 1),
    (3, 'John Lennon', 2);

Then we create two models, first Shop.php:

/**
 * Shop model
 */
class Shop extends \yii\db\ActiveRecord 
{

    /**
     * Define default database for the sake of clarity
     */
    public static function getDb() {
        return \Yii::$app->db;
    }

    /**
     * Relation to Employee
     *
     * @return Relation
     */
    public function getEmployees() {
        return $this->hasMany(Employee::className(), ['shop_id' => 'id']);
    }

}

And Employee.php:

/**
 * Employee model
 */
class Employee extends \yii\db\ActiveRecord 
{

    /**
     * Use different database 
     */
    public static function getDb() {
        return \Yii::$app->db2;
    }

    /**
     * Relation to Shop
     *
     * @return Relation
     */
    public function getShop() {
        return $this->hasOne(Shop::className(), ['id'=>'shop_id']);
    }
}

Now in a controller I would like to fetch some data with a criteria:

        $employeesShop1 = \app\models\Employee::find()
            ->joinWith(array('shop' => function($query) { return $query->andWhere(array('shop_id' => 1)); }))
            ->all();

This results in an error:

The statement would work if the query is created like this:

SELECT `employee`.* FROM `employee` LEFT JOIN `yii2_test1`.`shop` ON `employee`.`shop_id` = `yii2_test1`.`shop`.`id` WHERE `shop_id`=1

[mdmunir]

Of course you cannot. What you expected?
But try this

$employeesShop1 = \app\models\Employee::find()
            ->joinWith(['shop' => function($query) { 
                   return $query->from('yii2_test1.' . Shop::tableName())
                          ->andWhere(['shop_id' => 1]); 
            }])
            ->all();

I am not sure it will work :D

[samdark]

It is possible when using two MySQL instances as far as I know but the query should look as @mdmunir explained.

[samdark]

It definitely isn't possible if databases are of different types and I think we should throw an exception or fall back to WHERE IN queries in this case.

[stefandoorn]

In my case the db user/pass are the same, only difference is the database name (this is a requirement for this feature ofcourse).

I expected in this case an option to use the Shop model to find out the database name and form the query based on that. Might be too easy thought, but a feature in this direction could be interesting imo.

[qiangxue]

I think you should add database prefix when defining tableName() if you are using mysql. I don't think we support joining between tables from different database connections. (We do support relational queries that do not involve JOIN).

[samdark]

@qiangxue you're right about database prefix and where to define it.

[samdark]

Also it seems it should be a single connection in order to work properly.

[samdark]

Overall it's kinda rare situation and framework has enough support for it.

[keeper-evil]

/**
 * Shop model
 */
class Shop extends \yii\db\ActiveRecord 
{
    // ...
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return '{{' . static::getDb()->getSchema()->getRawTableName(parent::tableName()) . '}}';
    }
    // ...
}

And then

$employeesShop1 = \app\models\Employee::find()
    ->joinWith(array('shop' => function($query) {
        return $query->andWhere(array('shop_id' => 1));
    }))
    ->all();

[dynasource]

based on http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#cross-database-relations, I would expect cross database joining to work without using examples like this #5470 (comment)

[cebe]

@dynasource cross db JOIN is totally different from cross db relation. with() will work, but joinWith() does not.

If you want to use join on a dbms that supports it, you need to include the database name in the table name of the record:

public static function tableName()
{
      return '{{databasename}}.{{tablename}}';
}

implementing JOIN accross dbs on dbms that do not support it is not feasable in an efficient way.

[dynasource]

the insertion of that databasename in that tablename() can be quite cumbersome.

For example: I've got a plain Mysql scenario in which

• 95% of the tables are in the $app->db scope
• 5% of the tables are in the $app->db_stats scope

When I create i.e. an ActiveQuery in this $app->db_stats scope, it is already using the following code:

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

Because of this, when I create a ActiveQuery from that $app->db_stats scope, it assumes all the joinWith are in the same DB.

It seems the framework is not (yet) smart enough to include the DB name for all other tables with the default $app->db scope.

[cebe]

Doing that automatically would introduce a lot of overhead to support a very rarely used feature.

[dynasource]

IMHO, it is not rare to have multiple DB's of the same DBMS wired to an app. An since we support it anyways, it would be logical to have this working out of the box.

With respect to the overhead. That's another thing to be tackled, of course.

[keeper-evil]

@dynasource

/**
 * @property StatsItem[] $statsItems
 */
class Item extends \yii\db\ActiveRecord
{
    /**
     * @return \yii\db\ActiveQuery
     */
    public function getStatsItems()
    {
        return $this->hasMany(StatsItem::className(), ['stats_item_id' => 'id']);
    }
}

class StatsItem extends \yii\db\ActiveRecord
{
    /**
     * @inheritdoc
     */
    public static function getDb()
    {
        return \Yii::$app->get('db_stats');
    }

    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return '{{stats}}.{{' . self::getDb()->getSchema()->getRawTableName(parent::tableName()) . '}}';
    }
}

$items = \common\models\Item::find()
    ->joinWith('statsItems')
    ->limit(20)
    ->all();

[dynasource]

The problem is the other way around with StatsItem::find()

[keeper-evil]

@dynasource, I do not see a problem

class Item extends \yii\db\ActiveRecord
{
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return '{{site}}.{{' . self::getDb()->getSchema()->getRawTableName(parent::tableName()) . '}}';
    }
}

/**
 * @property Item $item
 */
class StatsItem extends \yii\db\ActiveRecord
{
    /**
     * @inheritdoc
     */
    public static function getDb()
    {
        return \Yii::$app->get('db_stats');
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getItem()
    {
        return $this->hasOne(Item::className(), ['id' => 'stats_item_id']);
    }
}

$statsItems = \common\models\StatsItem::find()
    ->joinWith('item')
    ->limit(20)
    ->all();

[dynasource]

the usecase is that Item returns a tableName like "item" in the default db application component scope

[keeper-evil]

For example: I've got a plain Mysql scenario in which

• 95% of the tables are in the $app->db scope
• 5% of the tables are in the $app->db_stats scope

Yes, this was required by condition. Item returns a tableName like item in the default db application component scope and StatsItem returns a tableName like stats_item in the your db_stats application component scope. I did not understand what's wrong.

[sdlins]

For MSSQL I had to do this:

public static function tableName()
{
    $defaultSchema = static::getDb()->schema->defaultSchema;
    return 'AGRO_PORTAL' . ($defaultSchema ? '.' . $defaultSchema: '') . '.VW_EMPREGADO';
}

But I will have to adapt my tests because, for simplicity, I have all tables in the same MySQL (not mssql) DB, not 20 dbs as we (unfortunately) have in prod server. A getDbName() (#6533) would be useful here too.

[rezikovka]

To automate this process in MySQL we can do smthng like this:

    public static function getDbName(): string
    {
        return self::getDb()->createCommand("SELECT DATABASE()")->queryScalar();
    }

    public static function tableName(): string
    {
        return '{{%' . self::getDbName() . '.table_name}}';
    }

Profit - we don't need to hardcode db-name into the model, just to define it in config. Request in getDbName() can be cached

[bizley]

Closing due to inactivity. If this should be reopen please write here.