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
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.