Re: Join Tables between db

On 1/26/2011 3:08 AM, Dave Maharaj wrote:

I have a site with 2 databases (same server)

1.       Being resources_db (data that never changes, Country, States, various selects and options to select from throughout the site)

2.       default_db users, profiles, things that can be changed, edited by users.

 

Now join tables happen to cross between resources_db and default_db where does the join tables go in these situations? How do you tell Cake where to look?

 

Hi Dave,
I use something similar in one of my apps. Here are some snippets from my code, slightly adapted to your example.

app/config/database.php
class DATABASE_CONFIG {

    var $default = array(
        'driver' => 'mysql',
        'persistent' => false,
        'host' => 'host',
        'login' => 'user',
        'password' => 'pass',
        'database' => 'default_db',
        'prefix' => '',
    );
....
    var $resource = array(
        'driver' => 'mysql',
        'persistent' => false,
        'host' => 'host',
        'login' => 'user',
        'password' => 'pass',
        'database' => 'resources_db',
        'prefix' => '',
    );
....
}

app/models/user.php
class User extends AppModel {
   ......
    public $belongsTo = array(
        'Country' => array(
            'className' => 'Country',
            'foreignKey' => 'country_id',
        ),
    );
....
}

app/models/country.php
class Country extends AppModel {

    public $useDbConfig = 'resource';
....
    public $hasMany = array(
        'User' => array(
            'className' => 'User',
            'foreignKey' => 'country_id',
            ....
        )
    );
....
}

With this setup I can do something like:

$this->User->find('random', array('contain' => array('Country'), 'limit' => 10));

HTH