cakephp2 join two tables from two database

Question

I have developing a SAAS based site, which i have to join two tables from two DBs, say table1 from DB1 and table2 from DB2. I have to get the matching records from table1 and table 2 using join in cakephp, but it throws error as below :

Error: SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'dbname'@'localhost' for table 'table_name' .

can anyone explain me how to done this using cakephp .

class table1 extends AppModel{ 
public $useDbConfig = 'DB1'; 
} 
Class table2 extends AppModel{ 
public $useDbConfig = 'DB2'; 


function desc(){
    $this->Store->useDbConfig = 'default';

    $rslted = $this->find('all',array(
        'conditions' => array('Jewel.id' =>1),
        'joins' => array(
            array(
                'alias' => 'Store',
                'table' => 'stores',
                'type' => 'INNER',
                'conditions' => 'Store.id = Jewel.store_id'
            )
        )
    ));

    return $rslted;
}
}

while called the desc function from controller is not working throws error:

Base table or view not found: 1146 Table 'site1.site1_stores' doesn't exist

but using the hasmany or belongsto on model will working , the join query is not working in controller

Anubhav · Answer 1 · 2014-01-07T08:26:49.117

1

Please follow the steps:

Step 1: Create two models named Jewel.php and Store.php for model classes

Content of Jewel.php 
class Jewel extends AppModel{

    public $useDbConfig = 'DB1';

}
Content of Store.php 
Class Store extends AppModel{

    public $useDbConfig = 'DB2';

}

Step 2: Create one method in Store model as shown below

function getData(){
   $this->bindModel(array(
                'hasOne' => array(
                  'Jewel' => array(
                           'foreignKey' => false,
                           'conditions' => array('Store.id = Jewel.store_id')
                  )
                 )
          ));
   $returnData = $this->find('all',array('conditions' => array('Jewel.id' =>1)));
}

Hope this will help!

edited Jan 07 '14 at 08:26

answered Jan 06 '14 at 06:42

Anubhav

1,605
4
18
31

I tried the code you posted but got an error as SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'dbname'@'localhost' for table 'db2_table' – shyamkarthick Jan 06 '14 at 10:23
class table1 extends AppModel{ public $useDbConfig = 'DB1'; } Class table2 extends AppModel{ public $useDbConfig = 'DB2'; } $configs = array('db details'); $newDbConfig = $this->table2->dbConnect($configs,'test'); $this->Jewel->useDbConfig = 'test'; $cars = $this->Jewel->find('all'); // it will connect and print the second db table records But below i used join which is not working $tbles = $this->Store->query("select * from dbname.table1 as tb1 join db2.tble2 as tb2 where Store.id=1"); which throws error user@localhost not connect for tble2. – shyamkarthick Jan 06 '14 at 11:33
if its possible to join two tables of different DBs using cakephp? – shyamkarthick Jan 07 '14 at 06:46
Yes it is possible...http://stackoverflow.com/questions/1788072/cakephp-using-multiple-databases-for-models – Anubhav Jan 07 '14 at 06:54
i have used the above code which is not working for me , can you please tell where am going wrong – shyamkarthick Jan 07 '14 at 07:05
Edit you question and put some code which you are using for joins and db config sample. – Anubhav Jan 07 '14 at 07:17
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/44681/discussion-between-shyamkarthick-and-anubhav) – shyamkarthick Jan 07 '14 at 08:11

cakephp2 join two tables from two database

1 Answers1