[FIXED] Symfony passing array using findBy

Issue

I'm using Symfony 4 in my project and I'm trying to get the list of users by role which is passed as a query parameter but I'm stuck with the findBy function as I'm trying to pass an array into it

The user entity has a property called roles (code from User.php)

    /**
     * @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);
    }
    
    public function setRoles(array $roles): self
    {
        $this->roles = $roles;
    
        return $this;
    }

The controller :

    public function get_all_users(
        Request $request
    ) {
        if ($request->query->get('role')) {
            $selectedRole = $request->query->get('role');
            $users = $this->getDoctrine()
                ->getRepository(User::class)
                ->findBy(['roles' => $selectedRole])
                //->findBy(['roles' => ['ROLE_CANDIDATE', 'ROLE_USER']])
                //->findBy(['roles' => ['ROLE_CANDIDATE']])
                //->findBy(['roles' => 'ROLE_CANDIDATE'])
            ;
    
        //->findBy(['roles' => ['ROLE_CANDIDATE', 'ROLE_USER']])
        } else {
            //fetch all users
            $users = $this->getDoctrine()
                ->getRepository(User::class)
                ->findAll()
        ;
        }
        //Response
        return $this->json([
            'message' => 'Users fetched successfully',
            'users' => $users,
        ], 200, [], ['groups' => 'UserResponse']);
    }

the response is always empty when I try to get users by role

this is a screenshot of the table user to show how the roles are stored in the database

Solution

The findBy method use an exact correspondance. Plus your role are json encoded in database. So you could not retrieve your user by role like this.

You will have to use DQL and 'like' condition:

Here is some quick exemple you can use in your User directory if you whish to filter by multiple role. (each user having at list on of these role will be retrieve)

/**
 * @return User[]
 */
public function findUserByRole(array $roles): array
{
    $qb = $this->createQueryBuilder('user');
    $or = false;
    foreach ($roles as $key => $role) {
        if ($or) {
            $qb->orWhere('user.roles like :role' . $key)
               ->setParameter('role' . $key, '%' . $role . '%');
        } else {
            $qb->where('user.roles like :role' . $key)
               ->setParameter('role' . $key, '%' . $role . '%');
            $or = true;
        }
    }
    return $qb->getQuery()
              ->getResult();
}

change the or by and to match each user having exactly all of these role

Answered By - Florent Cardot