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

findFirst conditions with trim function

Hi, i have a problem with phalcon.

Product::findFirst([ 'conditions' => "trim(trailing '/' from url) LIKE :url:", 'bind' => ['url' => '%xxx%'] ]);

and i get

PHP Fatal error: Uncaught Phalcon\Mvc\Model\Exception: Syntax error, unexpected token STRING(/), near to ' from url) LIKE :url: LIMIT :APL0:', when parsing: SELECT [App\Models\Product].* FROM [App\Models\Product] WHERE trim(trailing '/' from url) LIKE :url: LIMIT :APL0: (115) in

That trim syntax is only valid for MySQL, and Phalcon has it's own query abstraction layer (PHQL). Afaik, PHQL implementation only accepts one parameter and trims both leading and trailing characters. The reason for this is to abstract away the DB specific engine.

You should create a dialect class and implement your own trim function (with a different name). Here is a REGEXP example: https://forum.phalcon.io/discussion/7793/error-custom-dialect

As @Lajos said, it's MySQL only feature.

Here is how you can achieve it with raw queries:

$sql = "SELECT * FROM `admin_users` WHERE TRIM(TRAILING '3' FROM username) LIKE :username LIMIT 1";
$params = [
    'username' => '%niki%'
$model = new \Models\AdminUsers();
$test = new \Phalcon\Mvc\Model\Resultset\Simple(
    $model->getReadConnection()->query($sql, $params)

// Result
    [0] => Array
            [id] => 1
            [type] => developer
            [names] => Niki Mihaylov
            [username] => niki
            [password] => $2y$08$WVZIc1U0L1Ezd1BpSytXLucfBK0XWbXXNXd6Zs6m9bo15i/22Hi7m
            [settings] => {"interfaceLanguage":"bg"}
            [last_activity] => 2016-07-18 13:15:57


Read more form the docs here: https://docs.phalcon.io/en/latest/reference/phql.html#using-raw-sql You can make a method in your Product model if you want or even in the base model.