Fosuserbundle: select users by roles

[karimdev]

i want select users by roles so i wrote this function in my user repository

public function getUsers($nombreParPage, $page){
$query= $this->createQueryBuilder('u')
->where('u.roles=:roles')
->setParameter('roles', array(User::ROLE_DEFAULT))
->orderBy('u.id', 'DESC')
->getQuery();
$query->setFirstResult(($page-1) * $nombreParPage)
->setMaxResults($nombreParPage);
return new Paginator($query);
}

I have no result despite i have several users in my DB

Thank you

[stof]

The way roles are stored in the DB (using the Doctrine array type which serializes the array in the DB) makes it quite hard to search by role currently (there might be some weird workaround for it, but I never tried to find it)

[gagarine]

Juste for referance https://github.com/KingCrunch/UserAdminBundle try to do that.

[webdevilopers]

As @sstok stated in issue #258 you can use the LIKE syntax:

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();
}

Source: http://stackoverflow.com/questions/9016914/symfony-2-fos-bundle-how-to-select-users-with-a-specific-role#16692911

Actually I expected Doctrine's DQL to understand and handle the $groups property so I can use a condition like this:

            ->where('pm.roles IN (:roles)')
            ->setParameter('roles', array('ROLE_PROJECT_MANAGER'))

Unfortunately it doesn't.

I'm not sure if this behaviour will change when roles will be stored in a 'json_array' planned in this PR: https://github.com/FriendsOfSymfony/FOSUserBundle/pull/1385

Do you know @stof ?

[sstok]

Even when storing them in JSON, the database needs to actually support JSON (Hello PostreSQL 9.3) before you can use something like pm.roles IN (:roles).

In case of PostgreSQL you'd be actually better of using text[] as that is supported in all versions back to 7.2, only problem with using an array data-type is that you can no longer use indexes directly.
You can use a functional indexes however which allows you to index Arrays containing a certain value like 'ROLE_SUPER_ADMIN'.

But its better to build your own UserManager in that case and use NativeSQL to utilize the power of the database :)