In CakePHP, is it possible to define an association between entities in different databases, using different connections? If so, how do you specify the connection when setting the ‘joinTable’ value? Using the connection name as a prefix seems to have no effect.
I am guessing, from my trials, that cross-database joins are not supported by CakePHP’s ORM, and that custom logic will be required to retrieve associated data.
I am working with three databases which historically have been separate, but have numerous mutual connections. I am wondering about the wisdom of combining them into a single database. It would certainly speed things up a lot in terms of being able to use the ORM for getting associated data.
Hi, yes joins between separate databases are fine in raw sql. I have also had success using the ORM with some associations by declaring the default connection in the model definition (table file). However I run into problems with cascading deletions in join tables. It seems the ORM does not always pick up the connection when building queries in such cases.
I could write raw SQL queries to surmount these problems but I have concluded that merging my databases will make my life significantly easier and that the advantages outweigh the potential drawbacks, which seem in this case to be relatively minimal.