Using Array Entity in Doctrine Query Builder

Question

How are you?
I created an array entity.

   /**
     * @Groups({"event_rating_question", "portal"})
     * 
     * @ORM\Column(type="json", nullable=true)
     */
    private ?array $eventTypeIds = [];

In the repository, I want to get the results if the request parameter is in that array entity.
So I created the repository function like:

public function findQuestionsByEventType(int $eventTypeId)
    {
        $queryBuilder = $this->createQueryBuilder('item')
            ->andWhere(':eventTypeId MEMBER OF item.eventTypeIds')
            ->setParameter('eventTypeId', $eventTypeId)
            ->orderBy('item.sortOrder', 'ASC');

        return $queryBuilder->getQuery()->getResult();
    }

But I have an error like below:
"Exception: [Semantical Error] line 0, col 88 near 'eventTypeIds': Error: Invalid PathExpression. Must be a CollectionValuedAssociationField."
If someone knows how to fix it, please let me know.
Thanks.

Answer 1

You cannot use a json field as an array in your DQL query. To do this, create a custom function and register it in your symfony app or use a ready-made extension DoctrineJsonFunctions. You need the function JSON_CONTAINS to check if the value is in your json array.

And so, if you decide to use DoctrineJsonFunctions, then here is an example of use:

Installation

composer require scienta/doctrine-json-functions

Declare a function in config/packages/doctrine.yaml

orm:
    dql:
        string_functions: 
            JSON_CONTAINS: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonContains

Now you can use the function in your repository

public function findQuestionsByEventType(string $eventTypeId)
{
    $queryBuilder = $this->createQueryBuilder('item')
        ->where("JSON_CONTAINS(item.eventTypeIds, :eventTypeId) = true")
        ->setParameter('eventTypeId', $eventTypeId)
        ->orderBy('item.sortOrder', 'ASC');

    return $queryBuilder->getQuery()->getResult();
}