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.

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))

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

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;
};


33.7k
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']);

Worked fine, thanks!!!