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

Multiple database

Hi guys.

I´m trying to create a multiple database conneciton to the same host but something is wrong.

In the services.php file I created the two database connections:

use Phalcon\Db\Adapter\Pdo\Mysql as DbAdapter;

$di->set('db', function () {
    $config = $this->getConfig();
    return new DbAdapter([
        'host' => $config->database->host,
        'username' => $config->database->username,
        'password' => $config->database->password,
        'dbname' => 'project'
    ]);
});

$di->set('dbclient', function () {
    return new DbAdapter([
        'host' => '127.0.0.1',
        'username' => 'username',
        'password' => 'password',
        'dbname' => 'c1'
    ]);
});

Then I created two models:

Cities.php

namespace Project\Models;

use Phalcon\Mvc\Model;

class Cities extends Model
{
    /**
     * ID
     * @var integer
     */
    public $id;

    /**
     * Name
     * @var string
     */
    public $name;

    public function initialize()
    {

    }
}

Clients.php

namespace Project\Models;

use Phalcon\Mvc\Model;

class Clients extends Model
{
    /**
     * ID
     * @var integer
     */
    public $id;

    /**
     * Name
     * @var string
     */
    public $name;

    /**
     *
     * @var integer
     */
    public $city_id;

    public function initialize()
    {
        $this->setConnectionService('dbclient');

        $this->belongsTo(
            'city_id',
            'Project\Models\Cities',
            'id',
            array('alias' => 'city')
        );
    }
}

Then in the controlles I have a method called list that I use:

ClientsController.php

namespace Project\Controllers;

use DataTables\DataTable;
use Project\Models\Clients;

/**
* List clients to datatables (ajax request)
*/
public function listAction()
{
    $this->view->disable();
    $request = $this->request;

    $query = "SELECT a.id,
              a.name,
              b.name as city
              FROM Project\Models\Clients a,
              Project\Models\cities b
               WHERE b.id = a.city_id";

     $resultset  = $this->modelsManager->createQuery($query)->execute();
     $dataTables = new DataTable();
     $dataTables->fromResultSet($resultset)->sendResponse();

}

But when I try to access clients/list I´m receiving this error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'project.clients' doesn't exist
#0 [internal function]: PDOStatement->execute()
#1 [internal function]: Phalcon\Db\Adapter\Pdo->executePrepared(Object(PDOStatement), Array, Array)
#2 [internal function]: Phalcon\Db\Adapter\Pdo->query('SELECT `a`.`id`...', NULL, NULL)
#3 [internal function]: Phalcon\Mvc\Model\Query->_executeSelect(Array, NULL, NULL)
#4 C:\site\project\app\controllers\ClientsController.php(202): Phalcon\Mvc\Model\Query->execute()
#5 [internal function]: Project\Controllers\ClientsController->listAction()
#6 [internal function]: Phalcon\Dispatcher->callActionMethod(Object(Project\Controllers\ClientsController), 'listAction', Array)
#7 [internal function]: Phalcon\Dispatcher->dispatch()
#8 C:\site\smartman\public\index.php(41): Phalcon\Mvc\Application->handle()
#9 {main}


85.5k

joins do not work between different servers.

getRelated() etc methods ( as they do a query by themselves ) should work

And how could I get the same result, all clients with state names, using getrelated() method? Could you please give me an example?



85.5k

foreach (Clients::find() AS $client ) {
    $cityRecord = $client->getCity();

    echo $cityRecord->name;
}

this should work

Thanks Izo.

I was searching the docs yesterday and I came with the same solution.

$clients = Clients::find();

            foreach ($clients as $client) {
                $data[] = [
                    "id" => $client->id,
                    "name" => $client->name,
                    "tradename" => $client->tradename,
                    "city" => $client->city->name,
                    "state" => $client->city->state->abbreviation
                ];
            }

            $dataTables = new DataTable();
            $dataTables->fromArray($data)->sendResponse();

But using this approach slow down the proccess.

I´m using datatables with magnxpyr adapter. With foreach causes problems when I try to use the datatables search function typing fast. If I type very slow the search does not return any error, but when I type it a bit faster I recevive the error:

DataTables warning: table id=clients - Invalid JSON response. For more information about this error, please see https://datatables.net/tn/1

I never used Phalcon pagination. I´ll take a look at it and see if it works better.