yogur yogur - 1 year ago 101
PHP Question

How to concatenate strings in phalcon php phql?

I have the following Phql query:

$persons = Person::query()
->columns([
'id' => 'id',
'name' => 'first_name || last_name'
])
->where("first_name LIKE :searchQuery:")
->orWhere("last_name LIKE :searchQuery:")
->bind(['searchQuery' => $searchQuery . '%'])
->execute();


The database used is an sqlite database.

The query works fine until I include the concatenation operator of sqlite
||


The exception thrown is:


Scanner: Unknown opcode 402


I have tried
CONCAT(first_name, last_name)
, but it throws the exception:


SQLSTATE[HY000]: General error: 1 no such function: CONCAT

Answer Source

Also much better is to use dialect extension:

namespace App/Dialect;

class Sqlite extends \Phalcon\Db\Dialect\Sqlite
{
    /**
     * Sqlite constructor.
     */
    public function __construct()
    {
        $this->registerCustomFunctions();
    }

    /**
     * Register Custom dialect functions
     */
    public function registerCustomFunctions()
    {
        $customFunctions = [
            'CONCAT_WS'  => 'ConcatWs',
        ];
        foreach ($customFunctions as $key => $value) {
            $className = 'App\\Dialect\\Extensions\\'.$value;
            $object = new $className;
            $this->registerCustomFunction($key, $object->getFunction());
        }
    }
}

namespace App/Dialect/Extensions;

class ConcatWs
{
    public function getFunction()
    {
        return function (Dialect $dialect, $expression) {
            $sql = '';


            $count = count($expression['arguments']);

            if (true !== $count >= 2) {
                throw new Exception('CONCAT_WS requires 2 or more parameters');
            }

            if (2 === $count) {
                return $this->getSqlExpression($expression['arguments'][1]);
            }

            $separator = array_shift($expression['arguments']);

            --$count;
            foreach ($expression['arguments'] as $argument) {
                $sql .= $this->getSqlExpression($argument);
                if (0 !== --$count) {
                    $sql .= ' || '.$this->getSqlExpression($separator).' || ';
                }
            }

            return $sql;
        };
    }
}

And then in your db service:

use App/Dialect/Sqlite as SqliteDialect;

$di->set('db', function() {
    return new Sqlite([
        // other options, like password etc
        'dialectClass' => SqliteDialect::class
    ]);
});
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download