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 · Accepted Answer · 2014-01-07 08:26:49Z

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 7, 2014 at 8:26

answered Jan 6, 2014 at 6:42

Anubhav

1,6234 gold badges18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

shyamkarthick Over a year ago

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 Over a year ago

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 Over a year ago

if its possible to join two tables of different DBs using cakephp?

Anubhav Over a year ago

Yes it is possible...stackoverflow.com/questions/1788072/…

shyamkarthick Over a year ago

i have used the above code which is not working for me , can you please tell where am going wrong

|

Collectives™ on Stack Overflow

cakephp2 join two tables from two database

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related