Phalcon db connection to multiple databases on same host

Question

I've recently started using Phalcon PHP Framework, and there is one thing that confuses me a lot.

How to set $di to use multiple databases on same host? For example how to make this query work

SELECT * FROM
DB1.TABLENAME_1 AS t1
LEFT OUTER JOIN DB2.TABLENAME_2 AS t2 ON t1.some_id = t2.other_id

I've registered connection to DB1 and I've loaded all Models and can easily access data from any Model, but when I'm trying to JOIN table from one db with table from second db I'm getting error Model 'TABLENAME_2' could not be loaded which belongs to DB2

here is my $di

$di->setShared('db', function () use ($config) {
    return new DbAdapter(array(
        'host' => $config->database->host,
        'username' => $config->database->username,
        'password' => $config->database->password,
        'dbname' => $config->database->dbname,
        'options' => array(
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
        )
    ));
});

Answer 1

You could add three code-line in your model, for example, I have two DB (DB1, DB2), DB1 is the primary db and this config is set in service.php:

service.php:

$di->set('db', function () use ($config) {
    return new DbAdapter(array(
        'host' => "localhost",
        'username' => "root",
        'password' => $config->database->password,
        'dbname' => "DB1",
        'charset' => "utf8"
    ));
});

//declare DB2:
$di->set('DB2', function () use ($config) {
    return new DbAdapter(array(
        'host' => "localhost",
        'username' => "root",
        'password' => $config->database->password,
        'dbname' => "DB2",
        'charset' => "utf8"
    ));
});

The DB2 is the secondary database you want to use which involves two tables (T1, T2). For any of its tables, you can generate a model.php (T1.php, T2.php) by Phalcon devTools. Therefore, for using from these tables you should add three code-line to that and then define the T1.php class:

T1.php (This is Your model):

class Events extends \Phalcon\Mvc\Model
{

//you can connect to the DB2 database
public function initialize()
{
    $this->setConnectionService('DB2');
} 
...