CODEWITHSUNDEEP
postgresqlsymfonydoctrinejsonb
Sébastien Grégoire
Sébastien Grégoire

Reputation: 21

How to use JSONB_ARRAY_ELEMENTS function in QueryBuilder with Symfony?

I try to use functions provided by https://github.com/martin-georgiev/postgresql-for-doctrine/ but Symfony returns this error :

Error: Class 'JSONB_ARRAY_ELEMENTS' is not defined.

Here is the code of my function in the repository file:

$query = $this->createQueryBuilder('p')
              ->from('JSONB_ARRAY_ELEMENTS(CAST(p.wp_syncs AS JSONB))', 'pwps')
              ->getQuery()
              ->getResult();

The function is activated in doctrine.yaml:

dql:
    string_functions:
        # ...
        JSONB_ARRAY_ELEMENTS: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbArrayElements

Upvotes: 2

Views: 917

Answers (1)

Alessandro Chitolina
Alessandro Chitolina

Reputation: 906

You can’t set a function or a subquery in the FROM clause of a Doctrine DQL query.

You should write a SQL query instead:

$connection = $entityManager->getConnection(); // Get the DBAL connection

// Create a DBAL Query builder.
// Note that this query builder is NOT the same of the query builder returned by the EntityManager.
$qb = $connection->createQueryBuilder();

$qb->select('*')
    ->from('jsonb_array_elements(CAST(p.wp_syncs AS JSONB))', 'pwps')
    ->getQuery()
    ->getResult();

In this case you don’t need to register the function in doctrine’s configuration.

Upvotes: 3

Related Questions