FOS bundle - How to select users with a specific role?

Question

I am using the FOS bundle and I want to retrieve all users with a given ROLE from the database.

What is the best way to do this?

Answer 1

Usage:

    'users' => $userRepository->findUsersToRole(["ROLE_ADMIN"]),

UserRepository.php

    public function findUsersToRole($role): array
        {
            $role = json_encode($role);
            return $this->createQueryBuilder('item')
                ->andWhere('item.roles like :role')
                ->setParameter('role', '%'.$role.'%')
                ->getQuery()
                ->execute();
        }

Answer 2

Here I give an alternative solution :
I find users of roles for a given array
In controller I call the function like that

$users = $userRepository->findUsersOfRoles(['ROLE_ADMIN', 'ROLE_SUPER_USER']);

Then in my repository I make a loop to generate condition and set the parameters :

public function findUsersOfRoles($roles)
{
    $condition = 'u.roles LIKE :roles0';
    foreach ($roles as $key => $role){
        if ($key !== 0){
            $condition .= " OR u.roles LIKE :roles".$key;
        }
    }

    $query = $this->createQueryBuilder('u')
        ->where($condition);
    foreach ($roles as $key => $role){
        $query ->setParameter('roles'.$key, '%"'.$role.'"%');
    }

    return $query->getQuery() ->getResult();
}

Answer 3

In case you need to filter users by role using a DQL filter in a YAML file (In EasyAdminBundle for instance)

entities:
    Admin:
        class: App\Entity\User
        list:
            dql_filter: "entity.roles LIKE '%%ROLE_ADMIN%%'"

Answer 4

Finally i solved it, following is an exact solution:

public function searchUsers($formData)
{
    $em = $this->getEntityManager();
    $usersRepository = $em->getRepository('ModelBundle:User');
    $qb = $usersRepository->createQueryBuilder('r');

    foreach ($formData as $field => $value) {
        if($field == "roles"){
            $qb->andWhere(":value_$field MEMBER OF r.roles")->setParameter("value_$field", $value);
        }else{
            $qb->andWhere("r.$field = :value_$field")->setParameter("value_$field", $value);
        }
    }
    return $qb->getQuery()->getResult();
}

Cheers!

Answer 5

Just add this in your UserRepository or replace $this->_entityName by YourUserBundle:User:

/**
 * @param string $role
 *
 * @return array
 */
public function findByRole($role)
{
    $qb = $this->_em->createQueryBuilder();
    $qb->select('u')
        ->from($this->_entityName, 'u')
        ->where('u.roles LIKE :roles')
        ->setParameter('roles', '%"'.$role.'"%');

    return $qb->getQuery()->getResult();
}

If you are using FOSUser Groups you should use:

/**
 * @param string $role
 *
 * @return array
 */
public function findByRole($role)
{
    $qb = $this->_em->createQueryBuilder();
    $qb->select('u')
        ->from($this->_entityName, 'u')
        ->leftJoin('u.groups', 'g')
        ->where($qb->expr()->orX(
            $qb->expr()->like('u.roles', ':roles'),
            $qb->expr()->like('g.roles', ':roles')
        ))
        ->setParameter('roles', '%"'.$role.'"%');

    return $qb->getQuery()->getResult();
}

Answer 6

You can use just this on your DQL:

SELECT u FROM YourFavouriteBundle:User u WHERE u.roles [NOT] LIKE '%ROLE_YOUR_ROLE%'

Of course with QueryBuilder it's more elegant:

// $role = 'ROLE_YOUR_ROLE';
$qb->where('u.roles [NOT] LIKE :role')
   ->setParameter('role', "%$role%");

Answer 7

As @Tirithen states, the problem is that you will not get the users that have an implicit role due to role hierarchy. But there is a way to work around that!

The Symfony security component provides a service that gives us all child roles for a specific parent roles. We can create a service that does almost the same thing, only it gives us all parent roles for a given child role.

Create a new service:

namespace Foo\BarBundle\Role;

use Symfony\Component\Security\Core\Role\RoleHierarchy;
use Symfony\Component\Security\Core\Role\Role;

/**
 * ReversedRoleHierarchy defines a reversed role hierarchy.
 */
class ReversedRoleHierarchy extends RoleHierarchy
{
    /**
     * Constructor.
     *
     * @param array $hierarchy An array defining the hierarchy
     */
    public function __construct(array $hierarchy)
    {
        // Reverse the role hierarchy.
        $reversed = [];
        foreach ($hierarchy as $main => $roles) {
            foreach ($roles as $role) {
                $reversed[$role][] = $main;
            }
        }

        // Use the original algorithm to build the role map.
        parent::__construct($reversed);
    }

    /**
     * Helper function to get an array of strings
     *
     * @param array $roleNames An array of string role names
     *
     * @return array An array of string role names
     */
    public function getParentRoles(array $roleNames)
    {
        $roles = [];
        foreach ($roleNames as $roleName) {
            $roles[] = new Role($roleName);
        }

        $results = [];
        foreach ($this->getReachableRoles($roles) as $parent) {
            $results[] = $parent->getRole();
        }

        return $results;
    }
}

Define your service for instance in yaml and inject the role hierarchy into it:

# Provide a service that gives you all parent roles for a given role.
foo.bar.reversed_role_hierarchy:
    class: Foo\BarBundle\Role\ReversedRoleHierarchy
    arguments: ["%security.role_hierarchy.roles%"]

Now you are ready to use the class in your own service. By calling $injectedService->getParentRoles(['ROLE_YOUR_ROLE']); you will get an array containing all parent roles that will lead to the 'ROLE_YOUR_ROLE' permission. Query for users that have one or more of those roles... profit!

For instance, when you use MongoDB you can add a method to your user document repository:

/**
 * Find all users with a specific role.
 */
public function fetchByRoles($roles = [])
{
    return $this->createQueryBuilder('u')
        ->field('roles')->in($roles)
        ->sort('email', 'asc');
}

I'm not into Doctrine ORM but I'm sure it won't be so different.

Answer 8

Well, if there is no better solution, I think I will go to a DQL query:

$query = $this->getDoctrine()->getEntityManager()
            ->createQuery(
                'SELECT u FROM MyBundle:User u WHERE u.roles LIKE :role'
            )->setParameter('role', '%"ROLE_MY_ADMIN"%');

$users = $query->getResult();

Answer 9

If you have this requirement and your user list will be extensive, you will have problems with performance. I think you should not store the roles in a field as a serialized array. You should create an entity roles and many to many relationship with the users table.