Retrieve associated model from different database using cakephp 3

I’m developping an application that will use two databases: A main database to store the app’s data and a second one where I’ll check usernames and other read-only stuff. So I set up two different connections in my app.php:

 'Datasources' => [
    'default' => [
        'className' => 'CakeDatabaseConnection',
        'driver' => 'CakeDatabaseDriverMysql',
        'persistent' => false,
        'host' => '192.168.2.31',
        'username' => 'srddev',
        'password' => 'srddev',
        'database' => 'srddev',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'cacheMetadata' => true,
        'log' => false,
        'quoteIdentifiers' => false,
    ],
'spd' => [
        'className' => 'CakeDatabaseConnection',
        'driver' => 'CakeDatabaseDriverMysql',
        'persistent' => false,
        'host' => '192.168.2.31',
        'username' => 'spddev',
        'password' => 'spddev',
        'database' => 'spddev',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'cacheMetadata' => true,
        'log' => false,
        'quoteIdentifiers' => false,
    ],

After that I put a method in my Model/Table/UsersTable.php so cake will know that this table uses a different database:

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

I’m using this table to authenticate users, it’s working fine. But now I want to associate this model with my “pedidos” (orders) model, from my main database, and it’s not working fine. I’m trying this in my PedidosController.php:

 public function index()
{
    $this->paginate = [
        'contain' => ['Users']
    ];
    $this->set('pedidos', $this->paginate($this->Pedidos));
    $this->set('_serialize', ['pedidos']);
}

But it’s not working. I’m getting the following error: Error: SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘srddev.users’ doesn’t exist

Well, I shouldn’t be looking for this table in the srddev database, it belongs to spddev, as I signaled in my UsersTable.php. How can I tell cakephp that this table should be queried in a different connection?

Ps.: Both “databases” are actually schemmas in the same mysql server. I tried to connect to spddev.users table using the default connection and configuring $this->table(‘spddev.users’);, but had no success.

Answer

You could try setting the strategy in the association to select. This might help. There’s no full support for cross schema associations yet. I’ve put some work into it but it won’t solve all cases.

I’m not sure in which CakePHP 3 version it got merged but I do recommend staying up-to-date with the release cycles.