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

Error custom dialect

Seems to me dialect is read after parse of query. So how put to work,

Module.php

 $di['db'] = function () use ($config) 
        {

            $eventsManager = new \Phalcon\Events\Manager();
            $eventsManager->attach('db', new DbLog);
            $dialect->registerCustomFunction(
                'REGEXP',
                function($dialect, $expression) {
                    $arguments = $expression['arguments'];
                    return sprintf(
                        " REGEXP(%s)",
                        $dialect->getSqlExpression($arguments[0])
                    );
                }
            );

            $connection =  new DbAdapter(array(
                "host" => $config->database->host,
                "username" => $config->database->username,
                "password" => $config->database->password,
                "dbname" => $config->database->dbname,
                "charset" => $config->database->charset,
                'dialectClass' => $dialect,
            ));

            $connection->setEventsManager($eventsManager);

            return $connection;
        };

Error

Phalcon\Mvc\Model\Exception: Syntax error, unexpected token IDENTIFIER(REGEXP), near to '(:pattern)', when parsing: SELECT * FROM \Apps\Models\ConteudosMeta WHERE chave REGEXP(:pattern) (69)

Query:

$model->_modelsManager->executeQuery("SELECT * FROM \\Apps\\Models\\ConteudosMeta WHERE chave REGEXP(:pattern)", ['pattern' => 'as']);
StackTrace
0   Phalcon\Mvc\Model\Query->parse()
1   Phalcon\Mvc\Model\Query->execute()
2   Phalcon\Mvc\Model\Manager->executeQuery(SELECT * FROM \Apps\Models\ConteudosMeta WHERE chave REGEXP(:pattern), Array([pattern] => as))


34.6k

The placeholder is being passed wrongly, it needs an extra (:)

$model->_modelsManager->executeQuery("SELECT * FROM \\Apps\\Models\\ConteudosMeta WHERE chave REGEXP(:pattern:)", ['pattern' => 'as']);

Also, how are you creating the $dialect?, I don't see any instantiation for this variable



9.7k
edited Jul '15

Ok i fixed :pattern:

I don't know but i saw that parse come first because if i put an exit; on di['db'] i got the same error, exit only works if i take of my custom dialect

Thanks

Error Phalcon\Mvc\Model\Exception: Syntax error, unexpected token IDENTIFIER(REGEXP), near to '(:pattern:)', when parsing: SELECT * FROM \Apps\Models\ConteudosMeta WHERE chave REGEXP(:pattern:) (70)

Module.php

$di['db'] = function () use ($config) 
{

    $eventsManager = new \Phalcon\Events\Manager();
    $eventsManager->attach('db', new DbLog);

    $dialect = new \Phalcon\Db\Dialect\MySQL();

    $dialect->registerCustomFunction(
        'REGEXP',
        function($dialect, $expression) {
            $arguments = $expression['arguments'];
            return sprintf(
                " REGEXP(%s)",
                $dialect->getSqlExpression($arguments[0])
            );
        }
    );

    $connection =  new DbAdapter(array(
        "host" => $config->database->host,
        "username" => $config->database->username,
        "password" => $config->database->password,
        "dbname" => $config->database->dbname,
        "charset" => $config->database->charset,
        'dialectClass' => $dialect,
    ));

    $connection->setEventsManager($eventsManager);

    return $connection;
};


34.6k
Accepted
answer
edited Jul '15

The problem is the syntax of the REGEXP as it works as an operator and not as a function, you have to slightly change the way of using it:

$dialect->registerCustomFunction(
    'REGEXP',
    function($dialect, $expression) {
        $arguments = $expression['arguments'];
        return sprintf(
            "%s REGEXP(%s)",
            $dialect->getSqlExpression($arguments[0])
            $dialect->getSqlExpression($arguments[1])
        );
    }
);

Usage:

$model->_modelsManager->executeQuery("SELECT * FROM \\Apps\\Models\\ConteudosMeta WHERE REGEXP(chave, :pattern:)", ['pattern' => 'as']);


9.7k

Worked fine, thanks!!!