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

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.

Concatenate columns using SQLite

I can't figure how to concatenate two columns in find conditions using Sqlite operator:

Model::find(['conditions' => "[name] || ' ' || [surname] LIKE :value:", 'bind' => ['value' => 'name surname']]);

That code always trhows Phalcon\Mvc\Model\Exception Scanner: unknown opcode 402.



1.9k

Probably it is not implemented by PHQL

Well you can always do just AND :)



1.0k
Accepted
answer

After some research solved using CONCAT_WS (MySQL syntax) in conditions and extending Sqlite Dialect class:

class Sqlite extends \Phalcon\Db\Dialect\Sqlite
{
    public function getSqlExpression(array $expression, $escapeChar = null, $bindCounts = null)
    {
        if ('functionCall' === $expression['type']) {
            switch (strtoupper($expression['name'])) {
                case 'CONCAT_WS':
                    return $this->getFunctionConcatWs($expression, $escapeChar, $bindCounts);
                    break;
            }
        }

        return parent::getSqlExpression($expression, $escapeChar, $bindCounts);
    }

    protected function getFunctionConcatWs(array $expression, $escapeChar = null, $bindCounts = null): string
    {
        $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;
    }
}

Example:

Model::find(['conditions' => "CONCAT_WS(' ', [name], [surname]) LIKE :value:", 'bind' => ['value' => 'name surname']]);

Well it's good idea to make PR with CONCAT_WS for sqlite dialect :)