Query Symfony users by role

Here are some rough notes on querying Symfony users by role. It's tricky because the roles are stored in a JSON array in the database, and Symfony does not allow using JSON functions by default.

In order to filter by roles, we first need to allow the JSON_CONTAINS function in queries.

First add this requirement:

composer require scienta/doctrine-json-functions

Then add this section to config/packages/doctrine.yaml:

@@ -9,6 +9,9 @@ doctrine:
         profiling_collect_backtrace: '%kernel.debug%'
     orm:
         auto_generate_proxy_classes: true
+        dql:
+            string_functions:
+                JSON_CONTAINS: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonContains
         enable_lazy_ghost_objects: true
         report_fields_where_declared: true
         validate_xml_mapping: true

Now we can add this query to \App\Repository\UserRepository:

    public function findEmployees(): array
    {
        return $this->createQueryBuilder('u')
            ->andWhere("JSON_CONTAINS(u.roles, :role, '$') = 1")
            ->setParameter('role', json_encode(['ROLE_EMPLOYEE']))
            ->getQuery()
            ->getResult();
    }