CakePHP 3 - Cross database ORM query with hierarchical data not possible even when relationships are defined

Question

CakePHP 3.7. I have an application which stores a hierarchical list of filters. A user can subscribe/un-subscribe from them.

2 separate databases - both of which are on the same server - are used to store this data. Both are defined in config/app.php:

'Datasources' => [
    // Hierarchical filters list (consists of 3 tables) stored in this DB
    'default' => [
        'driver' => 'Cake\Database\Driver\Mysql',
        'database' => 'db1_name',
        // ...
    ],
    // Filters a user has subscribed to (in 1 table) stored in this DB
    'subscriptions' => [
        'driver' => 'Cake\Database\Driver\Mysql',
        'database' => 'db2_name',
        // ...
    ],
]

I'm wanting to do a query which does a JOIN using tables from both databases so have used the information here How to use different datasources in a Query using cakephp3?

The hierarchical list of filters uses 3 tables (in the default DB). hierarchy is as follows:

• regulations
  • groups
    • filters

I've baked the appropriate Table classes and done what's described on the linked answer to use the appropriate connection. So I have this:

 // Model/Table/RegulationsTable.php
public function initialize(array $config)
{
    parent::initialize($config);

    $this->setTable(ConnectionManager::get('default')->config()['database'] . '.regulations');

    $this->hasMany('Groups', [
        'foreignKey' => 'regulation_id'
    ]);
}

// Model/Table/GroupsTable.php
public function initialize(array $config)
{
    parent::initialize($config);

    $this->setTable(ConnectionManager::get('default')->config()['database'] . '.groups');

    $this->belongsTo('Regulations', [
        'foreignKey' => 'regulation_id',
        'joinType' => 'INNER'
    ]);
    $this->hasMany('Filters', [
        'foreignKey' => 'group_id'
    ]);
}

// Model/Table/FiltersTable.php
public function initialize(array $config)
{
    parent::initialize($config);

    $this->setTable(ConnectionManager::get('default')->config()['database'] . '.filters');

    $this->belongsTo('Groups', [
        'foreignKey' => 'group_id',
        'joinType' => 'INNER'
    ]);

    $this->belongsToMany('TblRegulatoryAlerts', [
        'foreignKey' => 'f_id',
    ]);
} 

The table which stores the filters.id which the user is subscribed to is in the second database (see subscriptions key in config/app.php posted earlier). So again this has been done in the appropriate Table class:

// Model/Table/TblRegulatoryAlertsTable.php
public function initialize(array $config)
{
    parent::initialize($config);

    $this->setTable(ConnectionManager::get('subscriptions')->config()['database'] . '.tbl_regulatory_alerts');

    $this->belongsTo('Filters', [
        'foreignKey' => 'f_id',
        'joinType' => 'INNER'
    ]);
}

The problem I'm having is doing a query through the ORM. I want to find all of the rows that exist for a given user in TblRegulatoryAlertsTable and then do a JOIN to the other database to get the Regulations.* fields. Of course Regulations is at the top of the hierarchy and we're starting with records that correspond to Filters, so it needs to work back up the hierarchy (filters ---> groups ---> regulations).

TblRegulatoryAlertsTable.f_id corresponds to Filters.id (if there is a row it means the user has subscribed to that filter) and there is a TblRegulatoryAlertsTable.u_id to represent the user ID.

The Table classes appear to have these relationships defined correctly, unless I'm mistaken?

So I've taken a look at Eager Loading Associations Via Contain from the Cake docs.

But when I do this:

// Model/Table/RegulationsTable.php
public function getUserSubscriptionRegions($u_id = null)
{
    $data = $this->find()->select(['Regulations.id', 'Regulations.label'])->distinct(['Regulations.id', 'Regulations.label'])
            ->contain([
        'Groups' => ['Filters', 'TblRegulatoryAlerts']])
            ->enableHydration(false)
            ->toArray();
    return $data;
}

It says

The TblRegulatoryAlerts association is not defined on Groups.

Of course it isn't. Because it has to go through the hierarchy of Filters to get the Groups followed by the Regulations.

There's also the issue of how/where to pass in u_id? However that's not even going to be possible unless the JOIN will work.

So the question is - I'm not sure how to define this via the ORM. How can I write this in the ORM syntax?

Ironically I've written it as plain SQL query using https://book.cakephp.org/3.0/en/orm/database-basics.html#executing-queries and it works without any issue. The equivalent SQL is:

SELECT
  DISTINCT db1_name.regulations.id, db1_name.regulations.label 
FROM
  db1_name.groups
JOIN db1_name.regulations
  ON db1_name.groups.regulation_id = db1_name.regulations.id 
JOIN db1_name.filters
  ON db1_name.filters.group_id = db1_name.groups.id 
JOIN db2_name.tbl_regulatory_alerts 
  ON db2_name.tbl_regulatory_alerts.f_id = dev_hub_subdb.filters.id 
WHERE db2_name.tbl_regulatory_alerts.u_id = 1234

It would be nice to be able to use the ORM to write queries like this though. Or is it just one of those things that's too complex for the ORM and it's better to keep it as plain SQL?

For information:

1. The plain SQL queries above do not actually have db1_name etc hardcoded into them. If you are doing it like that then it's possible to use ConnectionManager::get('default')->config()['database']; to get the DB name dynamically. This was just to illustrate the SQL used.

2. The fact it's using 2 separate database isn't something that can be changed. It's a legacy system I'm working with and the DB's need to remain as-is. Clearly doing a cross database join is possible because the plain SQL executes without any issue.