multiple database relationship on field other than primary key in CakePHP

Question

I have a project that necessarily spans several databases.

One database has tables:

CREATE TABLE device {
  device_uid integer unsigned not null primary key auto_increment,
  os varchar(50),
  name varchar(50)
}

CREATE TABLE platform {
  platform_id integer unsigned not null primary key auto_increment,
  name varchar(50)
}

The other database has the table:

CREATE TABLE oses_platforms {
  platform_id integer unsigned not null primary key auto_increment,
  os varchar(50),
  platform_id integer unsigned
}

I've created Models for the tables and the relationship:

<?php
class Platform extends AppModel {

    var $name = 'Platform';
    var $useTable = 'platform';
    var $primaryKey = 'platform_id';
    var $useDbConfig = 'otherdb';

    var $hasOne = array(
        'OsesPlatform' => array(
            'className' => 'OsesPlatform',
            'foreignKey' => 'platform_id'
        )
    );
}

class Device extends AppModel {

    var $name = 'Device';
    var $primaryKey = 'device_uid';
    var $useDbConfig = 'otherdb';        

}

class OsesPlatform extends AppModel {

    var $useDbConfig = 'default';
    var $name = 'OsesPlatform';
    var $primaryKey = 'os';


        var $belongsTo = array(
                'Platform' => array(
                        'className' => 'Platform',
                        'foreignKey' => 'platform_id',                            
                ),
        );

        var $hasMany = array(
                'Device' => array(
                        'className' => 'Device',
                        'foreignKey' => 'os',
                        'dependent' => false,                            
                )
        );
}
?>

If all three tables resided in 'default' or 'otherdb', I could do it in the 'conditions' argument to a hasOne or belongsTo relationship from Device to OsesPlatform, and in fact the hasOne relationship between Platform and OsesPlatform works fine. However, it's the relationship between Device and Platform that I need to model.

Answer 1

Perhaps this page explains the code you are after: http://blog.4webby.com/posts/view/6/cakephp%5Fmodels%5Fusing%5Fmultiple%5Fdb%5Fconnections

Hope that helps.

Simon.

Answer 2

I tried to recreate your structure in cakeapp.com. Take a look at the schema at http://cakeapp.com/sqldesigners/sql/oses

But when I try to add a device in the automatically created app here: http://oses.cakeapp.com/cake/oses/devices/add it seems a little bit wrong.

You can change the DB structure with the password: "oses". And let cakeapp.com create the app automatically by clicking link #3: "Build the CakeApp application based on the settings and the SQL"

Maybe this helps you?

Answer 3

As it turns out, HABTM relationships across databases aren't officially supported in Cake (this from markstory on #cakephp). While it will work sometimes, it's not clear (at least to me) under what conditions it will work, and there are no plans to improve the support for this. So I'll have to do this another way.