How to filter objects based on the Symfony workflow's place which is an array?-CodePudding

I've been struggling with this for a while but can't find a clean way to do it, so I'm seeking for some help.

I have custom filters (ApiPlatform 2.5 and Symfony 5.1) on database API outputs, and I need to filter on the current workflow place, or status as you like, of each output.

The Status has the below structure, which is a symfony workflow's place :

Status = { "OPEN": 1 }

My issue is that the status is stored as an array in the DB, and I can't find a way to have the querybuilder finding a match.

I've tried to build locally an array to do an = , a LIKE or an IN :

$status['OPEN'] = 1;

$queryBuilder->andWhere(sprintf('%s.Status = :st', $rootAlias))
   ->leftJoin(sprintf('%s.Objs', $rootAlias), 'o')
   ->andWhere('o.Profile = :p')
   ->setParameters(array(
        'st' => $status,
        'p' => $profile
));

But no way :(

I implemented a workaround that works but I don't like it as I'm using workflows a lot and need a clean way to filter outputs. My workaround is fairly simple, when the status is writen as an array in the DB, I also store it as a string in another field called StatusText, then filtering on StatusText is easy and straight.

Status can have different contents obviously : OPEN, CLOSING, CLOSED, ...

Help appreciated !! Thanks

EDIT & Solution

As proposed by Youssef, use scienta/doctrine-json-functions and use JSON_EXTRACT :

composer require scienta/doctrine-json-functions

Important, that was part of my issue, use the Doctrine type json_array an not array to store the status or the state, however you call it, in the Database.

Integrate the alias provided inside the ApiPlatform custom filter :

$rootAlias = $queryBuilder->getRootAliases()[0];

$json_extract_string = "JSON_EXTRACT(".$rootAlias.".Status, '$.OPEN') = 1";

$queryBuilder->andwhere($json_extract_string )
->leftJoin(sprintf('%s.Objs', $rootAlias), 'o')
->andWhere('o.Profile = :p')
->setParameter('p', $profile);

CodePudding user response：

You need to ask Doctrine if the JSON array contains the status, but you can't do that with the QueryBuilder method. When you hit the ORM limitations you can use a Native Query with ResultSetMapping. It allows you to write a pure SQL query using specific features of your DBMS but still get entity objects. Or you can use scienta/doctrine-json-functions and use JSON_EXTRACT