CODEWITHSUNDEEP
doctrine-ormdoctrine
Davide Casiraghi
Davide Casiraghi

Reputation: 18087

Get users by role

I have an array of entities (groups) in an entity (user), and in my Query Builder I would like to retrieve users by role.

I'm trying with this:

public function findUsersByRole($role){
    $qb = $this->createQueryBuilder('u');
    $qb->select('u')
       ->where('u.roles LIKE :role')
       ->setParameter('role', $role);

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

But I don't get results.

I tried also:

public function findUsersByRole($role)
{
  $qb = $this->createQueryBuilder('u');
  $qb->select('u')
      ->where(':role MEMBER OF u.roles')
      ->setParameter('role', $role);
  return $qb->getQuery()->getResult();
}

But I'm getting the error:

[Semantical Error] line 0, col 62 near 'roles': Error: Invalid PathExpression. Must be a CollectionValuedAssociationField.

What am I doing wrong?

The roles are stored in the user table field roles field that is of JSON type, like this:

[
    "ROLE_USER",
    "ROLE_ADMIN"
]

This is the function of the User class that stores the user role in the database:

public function setRoles(array $roles){
    $this->roles = $roles;
}

This is the unit test I'm writing to test the testFindUsersByRole function:

public function testFindUsersByRole() {
    $user1 = $this->createUser(1, "Developer", "John", "Smith", "[email protected]");
    $user2 = $this->createUser(2, "Developer", "Joe", "Black", "[email protected]");
    $user3 = $this->createUser(3, "Developer", "Jack", "Blade", "[email protected]");

    $roles1 = ['ROLE_ADMIN','ROLE_USER'];
    $roles2 = ['ROLE_USER'];

    $user1->setRoles($roles1);
    $user2->setRoles($roles2);

    $response = $this->userRepository->findUsersByRole('ROLE_ADMIN');
    $this->assertCount(1, $response);

    $this->purge([$user1, $user2, $user3]);
}

Upvotes: 0

Views: 1024

Answers (1)

Davide Casiraghi
Davide Casiraghi

Reputation: 18087

I found out what I was doing wrong.
In my unit test I was not saving the users entities on the database after assigning them the roles. So I have fixed the unit test like this.

public function testFindUsersByRole() {
    $user1 = $this->createUser(1, "Developer", "John", "Smith", "[email protected]");
    $user2 = $this->createUser(2, "Developer", "Joe", "Black", "[email protected]");
    $user3 = $this->createUser(3, "Developer", "Jack", "Blade", "[email protected]");

    $roles1 = ['ROLE_ADMIN','ROLE_USER'];
    $roles2 = ['ROLE_USER'];

    $user1->setRoles($roles1);
    $user2->setRoles($roles2);

    $this->entityManager->persist($user1);
    $this->entityManager->persist($user2);
    $this->entityManager->flush();

    $response = $this->userRepository->findUsersByRole('ROLE_ADMIN');
    $this->assertCount(1, $response);

    $this->purge([$user1, $user2, $user3]);
}

This function then is the one that worked:

public function findUsersByRole($role){
    $qb = $this->createQueryBuilder('u');
    $qb->select('u')
       ->where('u.roles LIKE :roles')
       ->setParameter('roles', '%"'.$role.'"%');
    
    return $qb->getQuery()->getResult();
  }

Upvotes: 2

Related Questions