Is it possible to specify SEPARATOR and ORDER BY in GROUP_CONCAT() in QueryBulder?

[s-ohnishi]

I just tried the following in v5.0.0RC4

        $b = new SomeModel();
        $qry = $b->modelsManager->createBuilder()
            ->addFrom(SomeModel::class, 'B');
        $qry->columns(['B.*', 'colPair'=>"GROUP_CONCAT( col1, '=', col2 SEPARATOR ':')"]);

I got an error saying.
Syntax error, unexpected token IDENTIFIER (SEPARATOR), near to ' ':') ...

The same is true for ORDER BY.
Is it possible to specify SEPARATOR and ORDER BY in GROUP_CONCAT() in QueryBulder?

[niden]

GROUP_CONCAT is not available in PHQL. However, you can register it as a valid function.

Have a look at the second example here:

https://docs.phalcon.io/5.0/en/db-phql#custom-dialect

[s-ohnishi]

I understand that PHQL does not support GROUP_CONCAT.
Register it as a custom function.

I thought it was a very general syntax, but it can only be used with MySQL/mariaDB.
However, if it is not supported, I thought it would be nice if it passed the argument of GROUP_CONCAT() as it is without checking it.
If so, it is the programmer's responsibility to widely support each DMS dialect.
The real problem is that you are checking the arguments of GROUP_CONCAT() and they are not column names or literals, not GROUP_CONCAT() itself.

Thank you for letting me know how to avoid this.

[s-ohnishi]

The sample I was taught had a fixed number of arguments and was inconvenient to use, so I chose to concatenate all arguments.

I couldn't solve it well, so I'm calling the keyword as a literal.
$qry->columns(['B.*', 'detail'=>"GROUPCONCAT(T.id,':',T.name,':',R.sht, 'ORDER BY', T.id)"]);

Then, I set it as follows in the registration of the db service.

$di->setShared('db', function () {
    //  snip
    if ($db instanceof \Phalcon\Db\Adapter\Pdo\Mysql) {
        $dialect = $db->getDialect();
        $dialect->registerCustomFunction(
            'GROUPCONCAT',
            function ($dialect, $expression) {
                $cols = array();
                $opts = array();
                $flg = false;
                foreach($expression['arguments'] as $argument) {
                    $arg = $dialect->getSqlExpression($argument);
                    if ($flg or ($arg == "'ORDER BY'") or ($arg == "'SEPARATOR'")) {
                        $opts[] = trim($arg, "'");
                        $flg = true;
                    } else {
                        $cols[] = $arg;
                    }
                }
                $sql = ' GROUP_CONCAT(' .  implode(',', $cols) . ' ' . implode(' ', $opts) . ')';
                return $sql;
            }
        );
    }
    return $db;
});

It seems to be working for the time being, so I'll see how it goes for a while.
However, since the syntax is slightly different, it seems to be a mistake.
If you could tell me how to return a Resultset\Complex or Resultset\Simple or an array of the Model class using raw SQL in the static methods of the Model, I might be able to avoid making weird mistakes.