Filtering data by association when association is in a separate database

I'm working with multiple databases and not all associations live in the same database. For example TasksTable is in database 1, UsersTable is in database 2.

I've got this association to work with 'Select' strategy as suggested by cake docs, in the TaskModel association looks as follows:

'belongsTo' => [
'Users' => ['strategy' => 'select', 'foreignKey' => 'user_id', 'propertyName' => 'User'],
]

In model for each table I'm defining the default connection:

public static function defaultConnectionName(): string
{
    return 'database1'; //database2 for Users
}

This works well when I'm doing a find on Tasks and containing Users, brings back all the data as expected:

$tasks = $this->Tasks->find('all', [
'contain' => ['Users']
]);

But I'm running into problem when I'm trying to filter Tasks by Users association, I've tried matching() and InnerJoinWith() I get the following error for both:

    $tasks = $this->Tasks->find('all', [
    'contain' => ['Users']
    ])->matching('Users', function ($q) {
    return $q->where(['Users.id' => 1]);
});

    Base table or view not found: 1146 Table 'database1.users' doesn't exist

Why is looking for Users table on database1? Is there another method I should be using? Any pointers are much appreciated.

Solution

It does so because your code is generating joins, more obviously so with the innerJoinWith() method, given its name, but matching() does so too, and neither method is affected by the connection configuration, or the strategy option, the latter is only relevant for contain().

In case your DBMS supports cross-schema communication, one "workaround" could be to add the database name to the table name. So for example with MySQL you might be able to do something like this in TasksTable::initialize() and UsersTable::initialize() respectively:

$this->setTable('database1.tasks');

$this->setTable('database2.users');

There is no explicit out of the box support for this because it's not supported across all the DBMS' that CakePHP supports, and those DBMS that do support it, implement it differently.