We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

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