How to use JSONB_ARRAY_ELEMENTS function in QueryBuilder with Symfony?

Question

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

Alessandro Chitolina · Accepted Answer · 2021-09-14 18:30:03Z

3

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.

edited Sep 14, 2021 at 18:30

answered Sep 12, 2021 at 18:18

Alessandro Chitolina

90610 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sébastien Grégoire Over a year ago

I can't see the difference between your code and mine… Do I need to write a a raw SQL query instead?

Alessandro Chitolina Over a year ago

The query builder created by the connection is not the same returned by the entity manager: the first one builds a SQL query, the other builds a DQL query, which does not support functions and subqueries in the FROM clause. I've edited my answer to make it clearer.

Collectives™ on Stack Overflow

How to use JSONB_ARRAY_ELEMENTS function in QueryBuilder with Symfony?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related