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. https://github.com/cakephp/cakephp/issues/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.