I have a Users entity that contains a roles field. The field is defined as follows:
/**
* @var list<string> The user roles
*/
#[ORMColumn]
private array $roles = [];
I am using Symfony and a Postgresql database. In database field roles is converted to type json
I want to create a custom query that extracts all the ‘Users’ that have one of these two roles: “ROLE_ADMIN” and “ROLE_ADMIN_DELEGATION”.
How could I do it?
If I do the query directly in the database:
SELECT u.* FROM users u WHERE u.roles::jsonb @> '"ROLE_ADMIN"' OR u.roles::jsonb @> '"ROLE_ADMIN_DELEGATION"';
It works perfectly. But when trying to transform it into “doctrine” language it does not return any elements
public function findAdministrators(int $offset): Paginator
{
$roles = ["ROLE_ADMIN", "ROLE_ADMIN_DELEGATION"];
$qb = $this->createQueryBuilder('u');
$orWhere = [];
$parameters = [];
foreach ($roles as $i => $rol) {
$parameterName = 'rol' . $i;
$orWhere[] = 'JSON_CONTAINS(u.roles, :' . $parameterName . ')';
$parameters[$parameterName] = $rol;
}
$qb->where(implode(' OR ', $orWhere))
->setParameters(new ArrayCollection($parameters)) // Fix here
->setMaxResults(self::MEMBERS_PER_PAGE)
->setFirstResult($offset)
;
return new Paginator($qb);
}
David Bermudez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.