Search code examples
phpsymfonydoctrine-ormdoctrinedql

Proper reuse of Doctrine's custom DQL function parameters to prevent "Positional parameter at index XXX does not have a bound value"

I am using Doctrine (Symfony project if that matters) and have custom DQL function FOO() to does some math using provided parameters. All is fine as long as my native implementation is using every provided parameter only once, but as soon as my formula requires reusing parameters, I start to face Doctrine complaints about lack of bound value. Apparently it considers each reference to be separate argument or I am improperly telling it handle it other way.

My DQL's parse() is pretty simple:

public function parse(Parser $parser): void
{
    $parser->match(Lexer::T_IDENTIFIER);
    $parser->match(Lexer::T_OPEN_PARENTHESIS);
    $this->arg1 = $parser->ArithmeticPrimary();
    $parser->match(Lexer::T_COMMA);
    $this->arg2 = $parser->ArithmeticPrimary();
    $parser->match(Lexer::T_CLOSE_PARENTHESIS);
}

so is FOO()'s getSql() implementation:

public function getSql(SqlWalker $sqlWalker): string
{
    $formula = '%1$s + %2$s';
    return \sprintf($formula,
        $this->arg1->dispatch($sqlWalker),
        $this->arg2->dispatch($sqlWalker)
    );
}

But as soon as formula is more like:

$formula = '(%1$s + %2$s) * %1$s';

then the Doctrine bites back with:

Positional parameter at index XXX does not have a bound value.

Invocation goes like this:

$qb->addSelect('FOO(entity.property, :arg1)');
$queryParams['arg1'] = ....

$result = $qb->getQuery()->execute($queryParams);

Unfortunately Doctrine docs are quite enigmatic on that topic and I am simply not sure how to convince it to reuse already provided parameters. The question simply is: what do I miss-understand (or do not understand at all) here and how to properly implement toSql() to make Doctrine happy in such case?

Solution

  • As explained here, named parameters are turned into positional parameters in the generated SQL (you get ? characters). As a consequence, dispatch() must be called once for every value present in the SQL expression:

    public function getSql(SqlWalker $sqlWalker): string
{
    $arg1A = $this->arg1->dispatch($sqlWalker);
    $arg2 = $this->arg2->dispatch($sqlWalker);
    $arg1B = $this->arg1->dispatch($sqlWalker);
    $formula = '(%s + %s) * %s';
    return \sprintf($formula, $arg1A, $arg2, $arg1B);
}