union-query on multiple databases

[Rakasch]

Description

Tried to create a union query on two tables that are on different databases and get an error.
It does not seem to take into account the other DB prefix.

simplified Example:

CatsTable on DB cats.
DogsTable on DB dogs.

I did define the DB name in each Table with defaultConnectionName():
I can successfully query the tables individually.

Now I want to do a union query.

$catQuery = $this->fetchTable('Cats')->find()->order(['created'])->limit(10);
$dogQuery = $this->fetchTable('Dogs')->find()->order(['created'])->limit(10);

$query = $catQuery->unionAll($dogQuery)->order(['created'])->limit(10);

I get this error:
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'catsDb.dogsTable' doesn't exist.

CakePHP Version

4.4.18

PHP Version

8.1

[Rakasch]

ok seems thats basically not supported. Only with hacks :(
Joins dont work either.
#15515

[Rakasch]

I can do a workaround like this:

So basically the query works, cake just doesnt take the db prefix of defaultConnectionName.
Same issue for joins.

[markstory]

We don't support joins/unions across databases. Database connections could be in different physical machines and there is no way for the ORM to safely abstract operations across databases.