CakePHP 3 - Joining data across multiple databases

1.1k Views Asked by At

I am trying to retrieve some data from tables which exist in two separate databases. Nothing seems to work - even the suggestions from the official documentation.

Let's say we have two tables in two databases:

db1 (default)
  - orders
db2
  - customers

If we set up the customers table correctly with a different connection name:

class CustomersTable extends Table
{

    public static function defaultConnectionName() {
        return 'db2';
    }

A normal contain will not work:

$this->Customers->find()
    ->contain(['Orders'])
    ->where(['Customers.id' => 12345]);

Error: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'db2.orders' doesn't exist

Edit: This works in Cake 3.1 but not 3.3


The Cake documentation has a section about changing the fetching strategy to select, which would basically just run a separate query for the table in the other database (kind of a pointless solution for this anyway).

It doesn't seem to work either:

$this->Customers->find()
    ->contain([
        'Orders' => [
            'strategy' => 'select',
            'queryBuilder' => function ($q) {
                return $q->select(['field1', 'field2', 'field3']);
            }
        ]
    ])
    ->where(['Customers.id' => 12345]);

In this case, the result object of the query has an empty order key. What do I need to do to get this strategy to work?

0

There are 0 best solutions below