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. stackoverflow.com/questions/32033558/…. — ndm
– ndm, Commented Apr 14, 2016 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
– Bruno Lamps, Commented Apr 14, 2016 at 19:51

Marlinc · Accepted Answer · 2016-04-14 23:30:33Z

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, 2016 at 23:30

Marlinc

2393 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bruno Lamps Over a year ago

It works! Thanks @marlinc. It's curious that the cake book says this strategy is already the default one (book.cakephp.org/3.0/en/orm/associations.html).

Marlinc Over a year ago

@BrunoLamps Good to hear that it worked! Could you possibly open a pull request on the book? github.com/cakephp/docs/blob/master/en/models/…

Bruno Lamps Over a year ago

Done! I never did it before, so I'm not sure if something is missing. github.com/cakephp/docs/pull/3909

Ko Takuhou · Accepted Answer · 2021-10-11 07:13:53Z

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, 2021 at 7:13

Ko Takuhou

1

1 Comment

Martin Muñoz Over a year ago

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.

Collectives™ on Stack Overflow

Retrieve associated model from different database using cakephp 3

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related