belongsTo a different connection doesn't get picked up automatically

[mehov]

Description

What do I have

I have a table that uses a non-default connection. To make it worse, the connection name is a variable and is configured.

    $table = $tableLocator->get($tableName);
    $connection = \Cake\Datasource\ConnectionManager::get($tableDatasourceName);
    $table->setConnection($connection);

That table belongsTo another table in the same database. But neither table has the connection defined in the table class - because again, the connection name varies.

What do I do

$table->find('all', ['contain' => ['AnotherTable']])->first();

What I expect to happen

If the select strategy is join (the default), the other table is looked up within whatever datasource is set for the original table at the moment. (The original table is initialised with a default connection, which gets updated later using setConnection().)

What actually happens

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

Related

• 3.0 - Associations and different connections #5369 - unlike my issue, OP wants different datasources
• automatic join for belongsTo association with cross databases not work #5058 - basically a wontfix, but from 10 years ago

CakePHP Version

3.10

PHP Version

5.6

[mehov]

I do realise I'm reporting this for 3.10 which receives security updates only. Let me know if this got fixed in the newer versions?

[mehov]

A workaround to get going:

foreach ($table->associations() as $association) {
    $association->setConnection($association->getSource()->getConnection());
}

[markstory]

Let me know if this got fixed in the newer versions?

This has not been fixed to my knowledge.

If the select strategy is join (the default), the other table is looked up within whatever datasource is set for the original table at the moment. (The original table is initialised with a default connection, which gets updated later using setConnection().)

Are you expecting that the ORM will detect that the connection is not joinable and change the load strategy when the connection is defined at runtime?

[mehov]

Are you expecting that the ORM will detect that the connection is not joinable and change the load strategy when the connection is defined at runtime?

No no, that sounds too complicated, or I'm not understanding. Sorry about the confusion. Let me try to clarify.

1. Posts belongsTo Users, both tables in the same datasource that is not default

2. $posts = $tableLocator->get('Posts');
   $connection = \Cake\Datasource\ConnectionManager::get('custom_datasource');
   $posts->setConnection($connection);
   

3. $posts->find('all', ['contain' => 'Users']):

   • Reality: errors out because Users are looked up in the default datasource
   • Expectations: because Posts are in custom_datasource, and belong to Users, ORM looks up Users in the datasource of Posts

[markstory]

Expectations: because Posts are in custom_datasource, and belong to Users, ORM looks up Users in the datasource of Posts

So are you expecting that the ORM will cascade setConnection() calls to all related models?

[mehov]

So are you expecting that the ORM will cascade setConnection() calls to all related models?

Only if and when it makes sense?

Meaning:

• the select strategy would have been join
• the association type is belongsTo
• the benefit outweighs the risk