0

I have an application in CakePHP 3.5.13 which uses four different databases.

In my config/app.php I have configured two of these as follows:

'Datasources' => [ 
    'default' => [ 'database' => 'sdb5_hub_subdb', ... ],
    'db_orgs' => [ 'database' => 'dev_hub_subdb_orgs', ... ]
];

So this means there are 2 databases with the names sdb5_hub_subdb and dev_hub_subdb_orgs.

I'm attempting to do a query which does a join on a table in each database.

I have my Table entities configured as follows:

// src/Model/Table/TblListsSubstancesCommentsTable.php
public function initialize(array $config)
{
    $this->belongsTo('Substances', [
        'foreignKey' => 'substance_id',
        'joinType' => 'INNER'
    ]);
}

public static function defaultConnectionName()
{
    return 'db_orgs';
}


// src/Model/Table/SubstancesTable.php
public function initialize(array $config)
{
    $this->belongsTo('TblListsSubstancesComments', [
        'foreignKey' => 'substance_id'
    ]);
}

When I attempt to do the following with the ORM:

$query = $Substances->find()->select(['id' => 'Substances.id'])->distinct();

if ($this->request->getData('org_information')) {
    $org_information = $this->request->getData('org_information');
    $query = $query->matching('TblListsSubstancesComments', function ($q) use ($org_information) {
    return $q->where(['TblListsSubstancesComments.comment LIKE' => '%'.$org_information.'%' ]);
     });
 }

It's producing an SQL error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'sdb5_hub_subdb.tbl_lists_substances_comments' doesn't exist

I don't understand this because the table definitely exists. Furthermore my TblListsSubstancesCommentsTable.php has defaultConnectionName() in it to specify that it should use the db_orgs connection. So I assume it must know to go to the second database to load that table? It's like it's looking in the default database and not finding it, but I don't know why, because the Table entity is telling it where it needs to look.

Andy
  • 5,142
  • 11
  • 58
  • 131
  • 2
    It's still not supported: **https://stackoverflow.com/questions/32033558/how-to-use-different-datasources-in-a-query-using-cakephp3**. Connection configuration is only used for querying on the specific table, not for joining. – ndm May 29 '18 at 14:52
  • That's a pretty serious limitation. People do segment data into different databases! You can even bake things on different datasources which probably adds to the confusion/strangeness of that situation too. – Andy May 29 '18 at 14:54
  • Possible duplicate of [How to use different datasources in a Query using cakephp3?](https://stackoverflow.com/questions/32033558/how-to-use-different-datasources-in-a-query-using-cakephp3) – arilia May 29 '18 at 15:19

0 Answers0