我在实体(用户(中有一个实体(组(数组,在我的查询生成器中,我想按角色检索用户。
我正在尝试这个:
public function findUsersByRole($role){
$qb = $this->createQueryBuilder('u');
$qb->select('u')
->where('u.roles LIKE :role')
->setParameter('role', $role);
return $qb->getQuery()->getResult();
}
但我没有结果。
我也试过:
public function findUsersByRole($role)
{
$qb = $this->createQueryBuilder('u');
$qb->select('u')
->where(':role MEMBER OF u.roles')
->setParameter('role', $role);
return $qb->getQuery()->getResult();
}
但我得到了错误:
[语义错误]第0行,第62列,靠近"roles":错误:无效PathExpression。必须是CollectionValuedAssociationField。
我做错了什么?
角色存储在JSON类型的用户表字段角色字段中,如下所示:
[
"ROLE_USER",
"ROLE_ADMIN"
]
这是User类的函数,它将用户角色存储在数据库中:
public function setRoles(array $roles){
$this->roles = $roles;
}
这是我编写的单元测试,用于测试testFindUsersByRole函数:
public function testFindUsersByRole() {
$user1 = $this->createUser(1, "Developer", "John", "Smith", "smith@test.com");
$user2 = $this->createUser(2, "Developer", "Joe", "Black", "joe@test.com");
$user3 = $this->createUser(3, "Developer", "Jack", "Blade", "blade@test.com");
$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]);
}
我发现自己做错了什么
在我的单元测试中,在为用户分配角色后,我没有将用户实体保存在数据库中。所以我已经修复了这样的单元测试。
public function testFindUsersByRole() {
$user1 = $this->createUser(1, "Developer", "John", "Smith", "smith@test.com");
$user2 = $this->createUser(2, "Developer", "Joe", "Black", "joe@test.com");
$user3 = $this->createUser(3, "Developer", "Jack", "Blade", "blade@test.com");
$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]);
}
这个功能就是起作用的:
public function findUsersByRole($role){
$qb = $this->createQueryBuilder('u');
$qb->select('u')
->where('u.roles LIKE :roles')
->setParameter('roles', '%"'.$role.'"%');
return $qb->getQuery()->getResult();
}