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:
/** * @ORMEntity(repositoryClass=SalarieRepository::class) */ class Salarie { /** * @ORMId * @ORMGeneratedValue * @ORMColumn(type="integer") */ private $id; /** * @ORMColumn(type="string", length=255) */ private $nom; /** * @ORMColumn(type="string", length=255) */ private $prenom; /** * @ORMColumn(type="string", length=255) */ private $email; /** * @ORMColumn(type="string", length=255, nullable=true) */ private $telephone; /** * @ORMColumn(type="string", length=255) */ private $service; /** * @ORMColumn(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: AppEntitySalarie {#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"
?
Advertisement
Answer
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