Filter users by role in Symfony 5

Ever needed to retrieve a list of users from a Doctrine repository having a single role? Like, get all users with ROLE_ADMIN or ROLE_USER?

If you go the “easy way” filtering with LIKE you can expect some surprises: what will this function returns when $role = 'ADMIN' and you both have roles named ROLE_ADMIN and ROLE_SUPER_ADMIN ?

<?php
    public function findByRoleWrongWay(string $role)
    {
        return $this->createQueryBuilder('u')
            ->andWhere('u.roles LIKE :role')
            ->setParameter('role', 'ROLE_%"' . $role . '"%')
            ->getQuery()
            ->getResult();
    }

Yes, you get an array of users either with role ROLE_ADMIN or ROLE_SUPER_ADMIN.

Default User Symfony entity

If you, like me, use Symfony Maker Bundle and create your User entity with bin/console make:user you’ll get an entity similar to this one¹:

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Security\Core\User\UserInterface;

class User implements UserInterface
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="json")
     */
    private $roles = [];
    
    /**
     * @see UserInterface
     */
    public function getRoles(): array
    {
        $roles = $this->roles;
        // guarantee every user at least has ROLE_USER
        $roles[] = 'ROLE_USER';

        return array_unique($roles);
    }
}

For my application an entity like this doesn’t work for one simple reason: ROLE_USER is not saved in User table, and I cannot filter users by this value; a new user persisted to DB will result with an empty roles value, at least in MySQL 5.7.

$ bin/console doctrine:query:dql "select u.roles from App\Entity\User u where u.id='001cf6c4-2d97-4be8-8b62-851ae3265432'"

/Users/manuel/Sites/test/vendor/doctrine/common/lib/Doctrine/Common/Util/Debug.php:55:
array (size=1)
  0 =>
    array (size=1)
      'roles' =>
        array (size=0)
          empty

I have 2 options here:

Don’t use ROLE_USER, but declare a new role that inherits it
Persist ROLE_USER to database

Let’s analyze both.

A new role for base user

Symfony’s roles follow a hierarchy, so declaring a new role and using it as the base role can be done by modifying config/packages/security.yaml

# config/packages/security.yaml
security:
    # ...

    role_hierarchy:
        ROLE_BASE_USER:   ROLE_USER
        ROLE_ADMIN:       ROLE_BASE_USER
        ROLE_SUPER_ADMIN: [ROLE_ADMIN, ROLE_ALLOWED_TO_SWITCH]

Use the new base role where appropriate, like in isGranted checks

<?php
    $hasAccess = $this->isGranted('ROLE_BASE_USER');
    $this->denyAccessUnlessGranted('ROLE_BASE_USER');

(Actually, the user gets 2 roles: ROLE_USER and ROLE_BASE_USER)

Persisting ROLE_USER to database

My favourite solution is to persist the role in database by editing User entity, assigning a default role for every new user created:

--- a/src/Entity/User.php
+++ b/src/Entity/User.php
@@ -27,8 +27,10 @@ class User implements UserInterface

     /**
      * @ORM\Column(type="json")
+     *
+     * guarantee every new user at least has ROLE_USER
      */
-    private $roles = [];
+    private $roles = ['ROLE_USER'];

     /**
      * @var string The hashed password
@@ -74,8 +76,6 @@ class User implements UserInterface
     public function getRoles(): array
     {
         $roles = $this->roles;
-        // guarantee every user at least has ROLE_USER
-        $roles[] = 'ROLE_USER';

         return array_unique($roles);
     }

Now, when I create a new user, executing the same DQL as before, it lists the user role

$ bin/console doctrine:query:dql "select u.roles from App\Entity\User u where u.id='0092b32b-7e54-4a83-a374-275c18f1a335'"

/Users/manuel/Sites/test/vendor/doctrine/common/lib/Doctrine/Common/Util/Debug.php:55:
array (size=1)
  0 =>
    array (size=1)
      'roles' =>
        array (size=1)
          0 => string 'ROLE_USER' (length=9)

When I add admin role to this user I’ll see both roles:

$ bin/console doctrine:query:dql "select u.roles from App\Entity\User u where u.id='0092b32b-7e54-4a83-a374-275c18f1a335'"

/Users/manuel/Sites/test/vendor/doctrine/common/lib/Doctrine/Common/Util/Debug.php:55:
array (size=1)
  0 =>
    array (size=1)
      'roles' =>
        array (size=2)
          0 => string 'ROLE_USER' (length=9)
          1 => string 'ROLE_ADMIN' (length=10)

Migrating user table to add ROLE_USER

Symfony Maker bundle creates roles entity property as json², so I’m going to use JSON specific MySQL function to create the migration query:

UPDATE `user` SET `roles` = '["ROLE_USER"]' WHERE JSON_LENGTH(`roles`) = 0;

and verify that the existing users get the new role:

bin/console doctrine:query:dql "select u.roles from App\Entity\User u where u.id='001cf6c4-2d97-4be8-8b62-851ae3265432'"

/Users/manuel/Sites/test/vendor/doctrine/common/lib/Doctrine/Common/Util/Debug.php:55:
array (size=1)
  0 =>
    array (size=1)
      'roles' =>
        array (size=1)
          0 => string 'ROLE_USER' (length=9)

Searching for user role in JSON field

Doctrine 2 doesn’t come with support for MySQL JSON functions, but there is a package called ScientaNL/DoctrineJsonFunctions that add support for them.

Let’s require the package:

$ composer require scienta/doctrine-json-functions

and configure it in config/packages/doctrine.yaml:

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

Now I can use JSON_CONTAINS in my DQL, and I can write a better search method:

<?php
    public function findByRoleThatSucksLess(string $role)
    {
        $role = mb_strtoupper($role);

        return $this->createQueryBuilder('u')
            ->andWhere('JSON_CONTAINS(u.roles, :role) = 1')
            ->setParameter('role', '"ROLE_' . $role . '"')
            ->getQuery()
            ->getResult();
    }

What’s missing

The solution described in this post works only if all user roles are listed in the database, this has to be done manually.

Some properties methods has been removed for simplicity ↩︎
https://github.com/symfony/maker-bundle/blob/v1.20.0/src/Security/UserClassBuilder.php#L92 ↩︎