Retrieve associated model from different database using cakephp 3

Question

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' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        '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' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        '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.

What exatly does "_had no success_" mean? What happened? Generally the table name approach should work, given that the DBMS supports cross db joins. **http://stackoverflow.com/questions/32033558/how-to-use-different-datasources-in-a-query-using-cakephp3**. — ndm, Apr 14 '16 at 19:30
Hi, If I set **$this->table('spddev.users');** in my **UsersTable.php** and comment it's **defaultConnectionName** method, when I try to use the model I get this message: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'srddev.users' doesn't exist — Bruno Lamps, Apr 14 '16 at 19:51

score 6 · Accepted Answer · answered Apr 14 '16 at 23:30

6

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.

answered Apr 14 '16 at 23:30

Marlinc

240
3
10

1

It works! Thanks @marlinc. It's curious that the cake book says this strategy is already the default one (http://book.cakephp.org/3.0/en/orm/associations.html). – Bruno Lamps Apr 15 '16 at 12:08
@BrunoLamps Good to hear that it worked! Could you possibly open a pull request on the book? https://github.com/cakephp/docs/blob/master/en/models/associations-linking-models-together.rst – Marlinc Apr 15 '16 at 12:15
Done! I never did it before, so I'm not sure if something is missing. https://github.com/cakephp/docs/pull/3909 – Bruno Lamps Apr 15 '16 at 12:39

score 0 · Answer 2 · answered Oct 11 '21 at 07:13

0

It works in my project

class {YourTableNameInCamelCase}Table extends Table
{
    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->setTable('{YourDBName}.{YourTableName}');

And you don't need to set the strategy to 'select', because it may cause query restriction.

answered Oct 11 '21 at 07:13

Ko Takuhou

1

This works but partially. You should keep the table in the default schema (the one that is described in the default connection), because CakePHP does not changes all the queries that it use internally, like: SHOW FULL COLUMNS FROM table; The query should be: SHOW FULL COLUMNS FROM schema.table; The previous query is used before any query that executes, is used to validate the model internally. If you keep the table (with no data) in the default schema, there is no problems. – Martin Muñoz May 09 '22 at 17:18

Retrieve associated model from different database using cakephp 3

2 Answers2