CODEWITHSUNDEEP
oracle-databaseactiverecordyii2
DrBorrow
DrBorrow

Reputation: 958

Yii2: query across two databases

I have two databases (one in Oracle and one mySQL) and I somehow need to join the data.

The following query works:

$qry = oracleTableName::find()
  ->with('mysqlTableName')
  ->asArray()
  ->all();

and returns the following layout:

 [0]=> array(
    [id] => 1
    [name] => test
    [mysqlID] => 7
    [mysqlTableName] => array(
       [id]=>7
       [score]=>1
    )
  )

However, if I use the select statement, it fails, (saying that the column mysqlTableName.id is an invalid identifier):

$qry = oracleTableName::find()
  ->with('mysqlTableName')
  ->select([
       'oracleTableName.id as OracleID',
       'mysqlTableName.id as MysqlID',
       'mysqlTableName.score as Score'
   ])
  ->asArray()
  ->all();

How can I select from both databases (or "access" the mysql result) so that I have one output i.e.:

[0]=>array(
  [OracleID]=>1
  [MysqlID]=>7
  [Score]=>3

Thank you

UPDATE

Here is the actual query and outputs: NOTE: in this example, the table "MapInvestorToOpportunity" is mysql and the table "INVESTOR" is Oracle

This works fine:

$performance= MapInvestorToOpportunity::find()
    ->with('investor') 
    ->andWhere(['fk_opportunityID' => $this->fk_opportunityID])
    ->limit(5)
    ->asArray()
    ->all();

And yields the following output:

Array
(
    [0] => Array
        (
            [id] => 43797
            [uid] => 0451/0258_DD45834-99207
            [fk_opportunityID] => 3
            [status] => 1
            [fk_investorID] => 99207
            [investor] => Array
                (
                    [INVESTOR_ID] => 99207
                    [COUNTRY_ID] => US
                    [PRIMARY_INSTITUTION] => DD71233

I can clearly see the country ID. However, as soon as I select the country ID it fails:

$performance= MapInvestorToOpportunity::find()
    ->with('investor') // or ('investor INVESTOR')
    ->andWhere(['fk_opportunityID' => $this->fk_opportunityID])
    ->select([
         'fk_opportunityID',
         'fk_investorID',
         'map_investor_to_opportunity.INVESTOR_ID',
         'COUNTRY_ID', // or 'INVESTOR.COUNTRY_ID'
     ])
    ->limit(5)
    ->asArray()
    ->all();

Column not found: 1054 Unknown column 'INVESTOR.COUNTRY_ID' in 'field list'
The SQL being executed was: SELECT `fk_opportunityID`, `fk_investorID`, `INVESTOR`.`COUNTRY_ID` FROM `map_investor_to_opportunity` WHERE `fk_opportunityID`='3' LIMIT 5

My understanding is that it is not possible to join the data in a query because it is two different databases. However, I just wanted to make absolutely sure... it seems a little crazy considering the array output clearly shows the data from the Oracle database

Many thanks

Upvotes: 0

Views: 226

Answers (2)

rob006
rob006

Reputation: 22174

I don't think it is possible to create JOIN between two completely separate DBMS. with() will register eager loading rules, but it will not create actual join between two tables - it will perform two separate queries to obtain necessary models.

To create actual join you should use joinWith() instead of with(). 

$qry = oracleTableName::find()
  ->joinWith('mysqlTableName')
  ->select([
       'oracleTableName.id as OracleID',
       'mysqlTableName.id as MysqlID',
       'mysqlTableName.score as Score'
   ])
  ->asArray()
  ->all();

But this will most likely fail, since there is no support for cross-DB joins.

Probably the best what you could get is to query results separately and combine them at PHP level.

Upvotes: 1

Martin Dimitrov
Martin Dimitrov

Reputation: 1304

You should just add a table identifier after the table name. 

$qry = oracleTableName::find()
  ->with('mysqlTableName msql')
  ->select([
       'oracleTableName.id as OracleID',
       'msql.id as MysqlID',
       'msql.score as Score'
   ])
  ->asArray()
  ->all();

The reason for this is, the with clause accepts relation name, not Table name, which might be slightly, but enough different. If this doesn't work, please comment with the result of this query and I will edit the answer accordingly 

$qry = oracleTableName::find()
      ->with('mysqlTableName msql')
      ->select([
           'oracleTableName.id as OracleID',
           'msql.id as MysqlID',
           'msql.score as Score'
       ])
      ->createCommand()->rawSql;

Upvotes: 0

Related Questions