We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Change source to Phalcon\Mvc\Model\Query\Builder

I'm following problem. I need to run a query in the CLI, however this query must be performed in various databases. I change the database directly into the Model with setConnectionService($db) as in:

$obj = new MyModel1();
$obj->setConnectionService($db);

but I need to change the database in a query with join. I could not change the source of Phalcon\Mvc\Model\Query\Builder. I need to run a query like:

$obj = (new Builder())->from(array('m1'=>'MyModel1'))
    ->innerJoin('MyModel2', 'm1.id = m2.id_m1', 'm2') ...

This query should be run on multiple databases. Is there a way to do this? There is an equivalent function to setConnectionService?



79.0k

For such database juggling you'd need PDO-only approach I guess.



3.8k

Do I understand correctly - you want to perform the join between tables in different databases?



79.0k
edited Oct '16

That's what it seems. And database is bound to the connection itself, so even with PDO like in my example:

 private final function _connect(){
        $init = [\PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8'];
        $this->pdo = new \PDO("mysql:host={$this->db->connection->default->host};dbname={$this->db->connection->default->dbname};", $this->db->connection->default->username, $this->db->connection->default->password, $init);
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        $this->pdo->setAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_OBJ);
        $this->pdo->setAttribute(\PDO::ATTR_CASE, \PDO::CASE_UPPER);
    }

You'd need to have two separate PDO connections etc. And for that you need custom method specific to your use case of joining two databases.



796

Sorry, I guess I was not clear. what I need is to run the same query in different databases, but not between different database tables. I found a reasonable solution. All databases have the same structure, and all my Models are using a "ConnectionService" called db_client. So I'm storing the settings for each database in CliDI()->setShared('client_name'). So for every database I change the setting of "db_client" like this:

$diCli = $this->getDI();
$diCli->setShared('db_client', $diCli->getShared($cliente));

Thanks!



79.0k

Yeah, that was clear now :)

But in general, application should not be providing fail over capabilities of such kind in case of a DB downtime. Load balancers in front of DB should take care of such events. Thus, your app targets one single point of entry, no matter what node might go down in the background.