How to implement cross database schema the right way on CakePHP 4.x?

Need Help
1

Hi, I have an app which uses 1 shared database and 1 different particular database for each group.

Database: Shared_database
Tables:
users (FK group_id)
groups
machines (FK group_id)

Databases: Particular_database
Tables:
records (FK animal_id and Shared_database.group_id)
animals

I’ve managed to make it work by using Shared_database as the ‘default’ on config/app_local.php and adding this code to AppController.php:

public function beforeFilter(EventInterface $event)
{
if ($this->Auth->user()) {
$Groups = TableRegistry::getTableLocator()->get(‘Groups’);
$group = $Groups->get($this->Auth->user()[‘group_id’]);
$suffix = $grupo->suffix;
$prefix = ‘my_prefix’;
ConnectionManager::drop(‘default’);
ConnectionManager::setConfig(‘default’, [
…
‘database’ => $prefix . $suffix,
…
]);
}
}

Now its working but when I visit records/index I’m getting:
“SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘particular_database.groups’ doesn’t exist”
So I also added the next code but it’s still not working,

class RecordsTable extends Table
{
public function initialize(array $config): void
{
$this->belongsTo(‘Groups’, [
‘foreignKey’ => ‘group_id’,
//‘bindingKey’ => ???
‘strategy’ => ‘select’,
]);
}
}

class GroupsTable extends Table
{
public function initialize(array $config): void
{
parent::initialize($config);
$this->setTable(‘shared_database.groups’);
}
}

2

Add the connections to you configuration (likely in config/app.php)

Then Configure the table connections

Cake automatically uses select strategy then

4

Thank you so much, that was what I was looking for. For anyone in the same situation, I changed the code from the first post to this so now I dont drop and replace the default connection anymore.
The config/app_local.php now has as ‘default’ the shared database and I create the connection to the particular databases dynamically.

public function beforeFilter(EventInterface $event)
{
if ($this->Auth->user()) {

        $Groups = TableRegistry::getTableLocator()->get('Groups');

        $group = $Groups->get($this->Auth->user()['group_id']);

        $particularDatabaseName = 'prefix' . $grupo->suffix;

        ConnectionManager::setConfig($particularDatabaseName, [
              ....
            'database' => $particularDatabaseName,
              ....
        ]);
        AppController::$particularDatabaseName;
    }
}

public static $particularDatabase;

Now the tables that belong to each particular database have one of the following code

public static function defaultConnectionName(): string {
return AppController::$particularDatabase;
}

and the tables from the shared database have:

public static function defaultConnectionName(): string {
return ‘default’;
}

pd: Im aware that saving the $particularDatabase as a static variable in AppController is not a good practice, but it will stay that way until I find another way to do it.

1 Like
5

You can use Configure::write and Configure::read at least to have centralized

1 Like