Symfony find user by role (JSON array Doctrine property)

Question

I am doing a small project where I have an entity with a roles property which consists of an array.

What I am trying to do is, in some controller, find an existing entity which has a specific role inside of the roles array.

I am trying to use the findOneBy() method, but I can't seem to make it work, it always returns null even though entities with the specific role I'm trying to find exist.

Here is my entity and its properties:

/**
 * @ORM\Entity(repositoryClass=SalarieRepository::class)
 */
class Salarie
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $nom;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $prenom;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $email;

    /**
     * @ORM\Column(type="string", length=255, nullable=true)
     */
    private $telephone;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $service;

    /**
     * @ORM\Column(type="json")
     */
    private $roles = [];

    // Getters & setters
}

And here is an example of something I tried with findOneBy() inside a controller, that returns null:

$rolecheck = $this->salarieRepository->findOneBy(["roles" => ["ROLE_RESPONSABLE_RH"]]);

When I try with any other property of the entity which isn't an array it works well, if I do something like this:

$rolecheck = $this->salarieRepository->findOneBy(["nom" => "test"]);
dd($rolecheck);

It will show the right entity :

SalarieController.php on line 47:
App\Entity\Salarie {#1501 ▼
  -id: 6
  -nom: "test"
  -prenom: "test"
  -email: "test@test.test"
  -telephone: null
  -service: "Graphisme"
  -roles: array:3 [▼
    0 => "ROLE_RESPONSABLE_RH"
    1 => "ROLE_RESPONSABLE_SERVICE"
    2 => "ROLE_SALARIE"
  ]
}

Where we can also see it does have the roles array with the role I'm trying to find inside it.

Any clues on how I could try to find one entity which has the specific role "ROLE_RESPONSABLE_RH"?

Answer 1

Your $roles property is of type json, which means it is stored as this in your database:

["ROLE_RESPONSABLE_RH", "ROLE_RESPONSABLE_SERVICE", "ROLE_SALARIE"]

You need to ask Doctrine if the JSON array contains the role, but you can't do that with the findOneBy() 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.

Create this method in your SalarieRepository class:

public function findByRole(string $role): array
{
    // The ResultSetMapping maps the SQL result to entities
    $rsm = $this->createResultSetMappingBuilder('s');

    $rawQuery = sprintf(
        'SELECT %s
        FROM salarie s 
        WHERE /* your WHERE clause depending on the DBMS */',
        $rsm->generateSelectClause()
    );

    $query = $this->getEntityManager()->createNativeQuery($rawQuery, $rsm);
    $query->setParameter('role', $role);
    return $query->getResult();
}

Then you need to replace the comment I put in the WHERE clause depending on the DBMS:

MariaDB - JSON_SEARCH():

SELECT %s
FROM salarie s 
WHERE JSON_SEARCH(s.roles, 'one', :role) IS NOT NULL

MySQL - JSON_CONTAINS():

SELECT %s
FROM salarie s 
WHERE JSON_CONTAINS(s.roles, :role, '$')

Warning: you must enclose the role parameter with double quotes:

$query->setParameter('role', sprintf('"%s"', $role));

PostgreSQL - jsonb escaped "?" operator:

SELECT %s
FROM salarie s 
WHERE s.roles::jsonb ?? :role

Warning: will require PHP 7.4+. See the RFC

Answer 2

1. CAST JSON to TEXT

class JSONText extends FunctionNode
{
    private $expr1;

    public function getSql(SqlWalker $sqlWalker)
    {
        return sprintf(
            "CAST(%s AS TEXT)",
            $this->expr1->dispatch($sqlWalker)
        );
    }

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->expr1 = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

2. Add to your Doctrine DQL:

dql:
    string_functions:
        JSON_TEXT: YOUR_NAMESPACE\JSONText

3. Use your cast function

$qb->andWhere("JSON_TEXT(d.topics) LIKE '%$role%'")