How to use different datasources in a Query using cakephp3?

Question

I'm working on a cakePHP3 project which does have 3 different datascources. I have one main model, called application, which should have two hasOne() associations to two models with a different datascource as Model Application. I have created the two models and pointed the two Model Tables to their datascources with defaultConnectionName().

Now I added two hasOne() relations to my ApplicationsTable object and recieve an sql error, when trying to Applications->get(). This is clear as in the SQL Statement their isn't any stating of a datasource on the FROM and JOIN part, like SELECT * FROM datasource1.myTable

I had a look into the cakephp framework ORM/Query class and the Query object only seems to have one datasource connection as a class attribute.

Is there a way to use different datascources in data retrieval using cake ORM or should I just use a custom query here?

Thanks in advance!

Answer 1

For now, CakePHP doesn't take datasource configurations into account when creating joins, and I don't think that this will be added in the near future, not least because cross database joins aren't supported "out of the box" (as in, just prepend the database name and you're set) in Postgres and SQLite.

Assuming you are using a DBMS that supports cross DB joins, what you could do is change the used table name to include the database name too, ie databaseName.tableName instead of just tableName

public function initialize(array $config)
{
    $this->table('databaseName.tableName');
    // ...
}

or dynamically

$this->table($this->connection()->config()['database'] . '.tableName');

SQLite

For SQLite you can get this working rather easily using the ATTACH DATABASE statement, as can be seen in the linked answer above. In your CakePHP application, you could issue this statement in your bootstrap or wherever you need id, something like

use Cake\Datasource\ConnectionManager;

// ...

/* @var $connection \Cake\Database\Connection */
$connection = ConnectionManager::get('default');
$connection->execute('ATTACH DATABASE "db2.sqlite3" AS databaseName');

which would attach the database db2.sqlite3 with a schema name of databaseName. From there on, the above mentioned table name solution should work fine, at least the non-dynamic one, as the dynamic one would use something like db2.sqlite3 as the schema name, which wouldn't work.

Postgres

I'm not used to Postgres, so at this point I can't give you an example, but it should probably work similarily using foreign data wrappers, ie issue the proper statements initially, and then just refer to the specified schema name.