We are moving our forum in GitHub Discussions. For questions about Phalcon v3/v4 you can visit here and for Phalcon v5 here.

Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Database Adapter not setting schema in PostgreSQL

Hi.

I use PostgreSQL as my application's database server, but the database adapter I create don't set the schema for the models and I need to do it manually using the models manager, how can I avoid setting the schema manually for every model?

This is how I'm creating the Adapter

my database config:

    'database' => [
        'host'     => 'localhost',
        'username' => 'username',
        'password' => 'password',
        'name'     => 'mydbname',
        'schema'   => 'app'
    ],

db connection in my app bootstrap:

        // Database
        $di->set('db', function () use ($config) {
            return new Phalcon\Db\Adapter\Pdo\Postgresql(array(
                'host'     => $config->database->host,
                'username' => $config->database->username,
                'password' => $config->database->password,
                'dbname'   => $config->database->name,
                'schema'   => $config->database->schema,
            ));
        });

This doesn't work!!!

$operators = new Operator;
$operator = $operators->findFirstByEmail($credentials['email']);

This works!!!

$operators = new Operator;
$app->modelsManager->setModelSchema($operators, 'myschema');
$operator = $operators->findFirstByEmail($credentials['email']);

If I don't set the schema manually then the model object can't find the tables:

Table "operators" doesn't exist on database when dumping meta-data for Operators.

I think the script is always reading the public schema instead of the one I set up.

Thanks.

edited Oct '14

I've found this solution too:

adding following method to my models abstract class

  public function initialize() {
        $this->getModelsManager()->setModelSchema($this, 'the_schema_name');
    }

but i'm looking for a better final solution!!!

MAYBE IT'S A BUG!



98.8k
Accepted
answer

Set your connection as:

$di->set('db', function () use ($config) {
    $connection = new Phalcon\Db\Adapter\Pdo\Postgresql(array(
        'host'     => $config->database->host,
        'username' => $config->database->username,
        'password' => $config->database->password,
        'dbname'   => $config->database->name,
        'schema'   => $config->database->schema,
    ));

    $connection->execute('set search_path=$user,public,' . $config->database->schema . ';');
    return $connection;
});

JustPostgreSQLThings

Dear @Phalcon I've accepted your solution but in action I saw that the following solution (that i said before) is more reliable and your solution didn't works 100% and Table "....." doesn't exist on database when dumping meta-data for ...... appears in some cases again.

public function initialize() {
    $this->getModelsManager()->setModelSchema($this, 'the_schema_name');
}