[SOLVED] How to escape curly braces in Atk4 DQL

How to escape curly braces in Atk4 DQL

I have the following code in Atk4 model:

$sql = 'REPLACE(\'[dnum]\', \'{DD}\', LPAD(DAY([issue_date]), 2, \'0\'))';
$f = $this->addExpression('calc_document_number',
    [$sql, 'type' => 'string', 'read_only' => true]);

The code above should replace {DD} in the dnum column with padded issue_date column. For search/sort reasons in database directly.

It seems that the {DD} part of the SQL is currently parsed/processed by Atk4. Is it possible to escape the curly braces so Atk4 will ignore them?

note: \{DD\} does not work

Solution

Yes you're right. Escaping braces is not implemented in DSQL not in Data. You can work around this issue by using expr() method directly and passing {DD} as a parameter (which will not be replaced once again).

This way it works for me:

$expr = $model->expr('REPLACE([dnum], [], LPAD(DAY([issue_date]), 2, \'0\'))',[
    '{DD}',
]);

$f = $model->addExpression('calc_document_number',
    [$expr, 'type' => 'string', 'read_only' => true]);
// here is no need to set type=string and read_only=true because expression field will automatically be readonly and with string type

echo $m->action('select')->getDebugQuery();

Also created a ticket to future reference: https://github.com/atk4/dsql/issues/144