'Invalid schema name' error thrown by Doctrine, but the raw SQL seems to work

Question

I'm using some custom DQL functions to filter rows by some JSONB fields in PostgreSQL. Here's my query function:

private function findTopLevelResources(): array {
    return $this->createQueryBuilder('r')
        ->where("JSON_EXISTS(r.contents, '-1') = FALSE")
        ->getQuery()
        ->getResult();
}

Running this code results in DriverException from AbstractPostgreSQLDriver:

An exception occurred while executing 'SELECT r0_.id AS id_0, r0_.marking AS marking_1, r0_.contents AS contents_2, r0_.kind_id AS kind_id_3 FROM resource r0_ WHERE r0_.contents?'-1' = false':

SQLSTATE[3F000]: Invalid schema name: 7 ERROR: schema "r0_" does not exist
LINE 1: ... r0_.kind_id AS kind_id_3 FROM resource r0_ WHERE r0_.conten...
                                                             ^

I tried to execute the raw SQL query manually from PHPStorm and it worked, no errors.

How do I get this to work in Doctrine?

Why doesn't this query work with Doctrine, but does when I test it manually?

Here's JSON_EXISTS: (based on syslogic/doctrine-json-functions)

class JsonExists extends FunctionNode
{
    const FUNCTION_NAME = 'JSON_EXISTS';
    const OPERATOR = '?';

    public $jsonData;
    public $jsonPath;

    public function getSql(SqlWalker $sqlWalker)
    {
        $jsonData = $sqlWalker->walkStringPrimary($this->jsonData);
        $jsonPath = $this->jsonPath->value;
        return $jsonData . self::OPERATOR . "'$jsonPath'";
    }

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->jsonData = $parser->StringPrimary();

        $parser->match(Lexer::T_COMMA);

        $this->jsonPath = $parser->StringPrimary();

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

Registered via Symfony's YAML config like this:

doctrine:
  orm:
    dql:
      numeric_functions:
        json_exists: Syslogic\DoctrineJsonFunctions\Query\AST\Functions\Postgresql\JsonExists

Versions of stuff:

PHP 7.1.1
doctrine/dbal v2.6.1
doctrine/orm dev-master e3ecec3 (== 2.6.x-dev)
symfony/symfony v3.3.4

gronostaj · Accepted Answer · 2017-08-03T13:16:23.230

The error message is a false clue.

Actual problem is caused by PDO (this is why it works from PHPStorm). When it sees a query like this:

SELECT * FROM foo WHERE contents?'bar'

It treats it like a parametrized query and the question mark ? as a parameter. For some reason it sometimes results in nonsensical error messages. This specific case could be solved by adding a space after the question mark, but it won't work for operators ?| and ?& which can't have a space in the middle.

The solution is to use functions corresponding to operators (this question saved the day). One can find out how they are called using queries like this one:

SELECT oprname, oprcode FROM pg_operator WHERE oprname IN ('?', '?|', '?&')

Here's the part of result related to JSON:

? → jsonb_exists
?| → jsonb_exists_any
?& → jsonb_exists_all

So instead of previous query which causes problems via PDO, one can use this equivalent one:

SELECT * FROM foo WHERE jsonb_exists(contents, 'bar')

'Invalid schema name' error thrown by Doctrine, but the raw SQL seems to work

1 Answers1