CakePHP - $hasAndBelongsToMany and $hasMany relationships between tables that reside on separate databases and have different querying languages?

Question

I have quite the complex problem to tell you about Stackoverflow, you see I have three tables that I need to define associations between for an application. These tables are: engineers, tickets, and testcases. I need to have a $hasMany relationship between engineers and tickets, and a hasAndBelongsToMany relationship between testcases and tickets. Here is the catch, engineers and testcases are both on a mysql database, while tickets is on a sqlite3 database(trac) on a separate server. The separate server part is not an issue, because we have the server mounted on the same machine that my application is on. I am basically wondering how you would setup these relationships, so that as each model is loaded its dependencies are loaded as well. I will literally use just about any solution that gets the job done. I am using CakePHP by the way.

Answer 1

I don't think it's a problem to work with 2 (or more) database connections in CakePHP.

Basically you need 2 connection strings in your /app/config/database.php i.e.:

class DATABASE_CONFIG {

    var $mysql = array(
        'driver' => 'mysql',
        'persistent' => false,
        'host' => 'localhost',
        'login' => 'user',
        'password' => 'password',
        'database' => 'database',
        'prefix' => '',
        'encoding'=>'utf8'
    );

    var $sqlite = array(
        'driver' => 'sqlite',
        'persistent' => false,
        'host' => 'localhost',
        'login' => 'user',
        'password' => 'password',
        'database' => 'database',
        'prefix' => '',
        'encoding'=>'utf8'
    );
}

btw. I am not quite sure about sqlite driver, but it should be this way.

And finally, you need to set to each model which connection to use. This could be done with:

class tickets extends AppModel {
   ...
   var $useDbConfig = 'sqlite';
   ...
}

And especially if you don't use any special SQL "hacks" it should work.

Answer 2

HABTM associations are not supported across multiple databases in CakePHP. In order to make the associations you will need to change the core. At least one person have achieved that. Look at his method.

Answer 3

You could just not define the association and do the querying manually. I.e., you associate the Ticket model with its HABTM join table TestcaseTickets in a belongsTo relationship (assuming they're both in the SQLite database) and query it manually:

$testcases = $this->Testcase->find(…);
$tickets = $this->Ticket->TestcaseTickets->find('all', array(
    'conditions' => array(
        'TestcaseTickets.testcase_id' => Set::extract('/Testcase/id', $testcases)
    )
));

It takes away a bit of convenience, but doesn't make a big difference in the end, especially if you do this automatically in the afterFind callback of the Testcase model.