2

So i have my database.php as follows:

public $default = array(
    'datasource' => 'Database/Mysql',
    'persistent' => false,
    'host' => 'SOMEHOST',
    'port' => 8889,
    'login' => 'LOGIN',
    'password' => 'PASSWORD',
    'database' => 'DB1',
    'prefix' => '',
    'encoding' => 'utf8',
);

public $db2 = array(
    'datasource' => 'Datasources.Freetds',
    'persistent' => false,
    'host' => 'SOMEHOST',
    'port'=> 1433,
    'login' => 'LOGIN',
    'password' => 'PASSWORD',
    'database' => 'DB2',
    'prefix' => '',
    'encoding' => 'utf8',
);

Then I have some SHELL code which tries to "find" some fields in one DB so that it can cross-check with other fields in the other DB, as follows:

(...)
$qry = $this->Model1->find('all', array(
        'fields' => array('Model1.id','Model1.field1'),
        'conditions' => array(
            'Model1.field2' => 'WAITING'),
        'recursive' => -1));

    $this->loadModel('Model2');
    $num_reg=count($qry);
    for ($loop=0; $loop<$num_reg;$loop++) {
        $registro = $this->Model2->find("first", array(
            'recursive' => -1,
            'fields' => array ('Model2.field1', 'Model2.field2'),
            'conditions' => array ('Model2.field1' => $qry[$loop]['Model1']['field1'])));
        var_dump($registro);
  (...)

Model1 uses $default and Model2 uses $db2. Model2.php follows (snippet):

public $useTable = 'Table';
public $primaryKey = 'key';
public $useDbConfig = "db2";

Unfortunately, CakePHP doesn't seem to be switching connections and/or DBs, since I get Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Model2.field1' in 'field list'.

Debugging showed me that CakePHP is trying to use the default DB when building the SQL statement (select ... from DEFAULT.MODEL2TABEL as MODEL2 ....).

Any ideas?

Thanks in advance!

Pbal
  • 185
  • 14

3 Answers3

1

I went through CakePHP's documentation and failed to understand why this isn't working, but I did find a suitable WORKAROUND, which is documented so I wouldn't rate it as "inelegant". If you don't agree with my answer, please don't "minus-1" me... This solution does work and solves my problem.

I will continue to look for the "right"(best) way and eventually post back here...

Anyway, CakePHP allows you to set your Model's Datasource "on the fly" and that was what ended up working for me.

Just after $this->loadModel('Model'); place a line like $this->Model->setDatasource('datasource'); and it worked like a charm.

This solved my problem in a SHELL, but since the method is under the Model Class, it will probably work in CONTROLLERs as well (http://api.cakephp.org/2.4/class-Model.html#_setDataSource)

Pbal
  • 185
  • 14
0

The problem here is that the method: 

$this->loadModel('Model');

somehow does not initiate the model correctly.

You should use the traditional way:

App:uses('MyModel', 'Model');
$this->MyModel = new MyModel();

I had a hard time figuring this out... This may also solve many other problems.
I suggest you choose to avoid using loadModel() in the future...

Tomas Gonzalez
  • 1,124
  • 15
  • 33
0

I just realized that you can't make joins between tables which belong to different dbs, even if you configure correctly the db connections.

The fact is that the query generator does not include the dbname in the query.

so, if you have:

  • db1.a ##this is a table a in db1
  • db2.b ##this is a table b in db2

what you get from cakephp2 ORM is this:

select a.id, b.id from a inner join b on (a.b_id == b.id) ##NOT WORKING

instead of

select a.id, b.id from db1.a a inner join db2.b b on (a.b_id == b.id) ##WORKING

My solution was to move the problem to the database. I simply create a view in db1 which maps table b of db2

CREATE VIEW b AS SELECT * FROM db2.b;

this way I can work normally in cakephp on table b, without knowing that the data comes from db2

massimoi
  • 324
  • 4
  • 11