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.

IF statemen in PHQL

I use syntax like

$phql = 'SELECT * FROM Product WHERE is_deleted = "no" AND IF(20 > 0, price < 20, NULL)

But result error. How can i use 'IF' statement like native MySQL query in PHQL?



10.6k
Accepted
answer
edited Feb '18

You need to register custom function (should be added in doc or faq somewhere), example for IF(condition, value_if_true, value_if_false) :

<?php
use Phalcon\Db\Dialect\MySQL as SqlDialect,
Phalcon\Db\Adapter\Pdo\Mysql;

$di->set('db', function () use ($config) {  
    $dialect = new SqlDialect();
    $dialect->registerCustomFunction('IF', function($dialect, $expression) {
            return sprintf(
                'IF('.$dialect->getSqlExpression($expression['arguments'][0]).', %d, %d)',
                $dialect->getSqlExpression($expression['arguments'][1]),
                $dialect->getSqlExpression($expression['arguments'][2])
             );
        }
    );
    return new Mysql([
        'host' => $config[ENV]->database->host,
        'username' => $config[ENV]->database->username,
        'password' => $config[ENV]->database->password,
        'dbname' => $config[ENV]->database->dbname,
        'charset' => $config[ENV]->database->charset,
        'dialectClass'=>$dialect
    ]);
});
$robots = Robot::find([
    'columns'=>('*, IF(id < 10, 1, 0) as isValid')
]);

You need to register custom function (should be added in doc or faq somewhere), example for IF(condition, value_if_true, value_if_false) :

<?php
use Phalcon\Db\Dialect\MySQL as SqlDialect,
Phalcon\Db\Adapter\Pdo\Mysql;

$di->set('db', function () use ($config) {  
   $dialect = new SqlDialect();
   $dialect->registerCustomFunction('IF', function($dialect, $expression) {
           return sprintf(
               'IF('.$dialect->getSqlExpression($expression['arguments'][0]).', %d, %d)',
               $dialect->getSqlExpression($expression['arguments'][1]),
               $dialect->getSqlExpression($expression['arguments'][2])
            );
       }
   );
   return new Mysql([
       'host' => $config[ENV]->database->host,
       'username' => $config[ENV]->database->username,
       'password' => $config[ENV]->database->password,
       'dbname' => $config[ENV]->database->dbname,
       'charset' => $config[ENV]->database->charset,
       'dialectClass'=>$dialect
   ]);
});
$robots = Robot::find([
  'columns'=>('*, IF(id < 10, 1, 0) as isValid')
]);

You make me loving more Phalcon :D