Association in cakephp multiple databases Cakephp 2

1
votes

How to get data form multiple databases.

Example- 

two database -> db_1 and db_2.
two tables -> users in db_1 database and countries in db_2   
    users table -> id , username, country_id, status
    countries table -> id, name, status

and Association is -

App::uses('AppModel', 'Model');

class User extends AppModel 
{
var $name = 'User';
var $useDbConfig = 'bd_1';
var $belongsTo = array('Country'=>array('className'=>'Country'), );
}

And in Controller when I use this Query then get a error

    $data = $this->User->find('all', array('conditions' => array('Country.status' => 1)));

    $this->set('data', $data);

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Country.status' in 'where clause

1
cakephpassociationscakephp-2.0multiple-databases
Can you give an example of the data you expect to receive from the above query? - Progredi Digital

1 Answers

0
votes

You can´t do this out of the box because if you configured the two databases in Config/database.php they will be in different connections.

What you can do is make two separated finds and join the array data in php or, if you really need to do this and the databases are in the same server, make a custom query:

$db = $this->User->getDataSource();    
$data = $db->fetchAll('SELECT * from db_1.users User LEFT JOIN db2.countries Country');