Symfony 按角色查找用户(JSON 数组 Doctrine 属性) [英] Symfony find user by role (JSON array Doctrine property)
问题描述
我正在做一个小项目,其中我有一个实体,其中包含一个由数组组成的角色属性.
我想要做的是,在某个控制器中,找到一个在角色数组中具有特定角色的现有实体.
我正在尝试使用 findOneBy()
方法,但我似乎无法使其工作,即使存在我试图找到的具有特定角色的实体,它也始终返回 null.
这是我的实体及其属性:
/*** @ORM\Entity(repositoryClass=SalarieRepository::class)*/工资等级{/*** @ORM\Id* @ORM\GeneratedValue* @ORM\Column(type=整数")*/私人 $id;/*** @ORM\Column(类型=字符串",长度=255)*/私人 $nom;/*** @ORM\Column(类型=字符串",长度=255)*/私人 $prenom;/*** @ORM\Column(类型=字符串",长度=255)*/私人 $email;/*** @ORM\Column(类型=字符串",长度=255,可为空=真)*/私人电话;/*** @ORM\Column(类型=字符串",长度=255)*/私人服务;/*** @ORM\Column(type=json")*/私人 $roles = [];//吸气剂 &二传手}
这是我在控制器中使用 findOneBy()
尝试的示例,它返回 null
:
$rolecheck = $this->salarieRepository->findOneBy([roles" => [ROLE_RESPONSABLE_RH"]]);
当我尝试使用不是数组的实体的任何其他属性时,如果我执行以下操作:
$rolecheck = $this->salarieRepository->findOneBy(["nom" => "test"]);dd($rolecheck);
它将显示正确的实体:
SalarieController.php 第 47 行:应用\实体\薪水{#1501 ▼-id: 6-nom:测试"-prenom:测试"-电子邮件:test@test.test"-电话:空-服务:Graphisme"-角色:数组:3 [▼0 =>ROLE_RESPONSABLE_RH"1 =>ROLE_RESPONSABLE_SERVICE"2 =>ROLE_SALARIE"]}
我们还可以看到它确实有角色数组,其中包含我试图在其中找到的角色.
关于我如何尝试找到具有特定角色 ROLE_RESPONSABLE_RH"
的实体的任何线索?
你的 $roles
属性是 json
类型,这意味着它在你的数据库中是这样存储的:
[ROLE_RESPONSABLE_RH"、ROLE_RESPONSABLE_SERVICE"、ROLE_SALARIE"]
您需要询问 Doctrine JSON 数组是否包含角色,但您不能使用 findOneBy()
方法这样做.
当您遇到 ORM 限制时,您可以使用 带有 ResultSetMapping 的本机查询.它允许您使用 DBMS 的特定功能编写纯 SQL 查询,但仍能获取实体对象.
在您的 SalarieRepository
类中创建此方法:
公共函数 findByRole(string $role): 数组{//ResultSetMapping 将 SQL 结果映射到实体$rsm = $this->createResultSetMappingBuilder('s');$rawQuery = sprintf('选择 %s来自薪水WHERE/* 你的 WHERE 子句取决于 DBMS */',$rsm->generateSelectClause());$query = $this->getEntityManager()->createNativeQuery($rawQuery, $rsm);$query->setParameter('role', $role);返回 $query->getResult();}
然后你需要根据DBMS替换我放在WHERE
子句中的注释:
MariaDB - JSON_SEARCH():
SELECT %s来自薪水WHERE JSON_SEARCH(s.roles, 'one', :role) 不是 NULL
MySQL - JSON_CONTAINS():
SELECT %s来自薪水WHERE JSON_CONTAINS(s.roles, :role, '$')
<块引用>
警告:您必须用双引号将 role
参数括起来:
$query->setParameter('role', sprintf('"%s"', $role));
PostgreSQL - jsonb 转义了?";运算符:
SELECT %s来自薪水WHERE s.roles::jsonb ??:角色
<块引用>
警告:需要 PHP 7.4+.请参阅RFC
I am doing a small project where I have an entity with a roles property which consists of an array.
What I am trying to do is, in some controller, find an existing entity which has a specific role inside of the roles array.
I am trying to use the findOneBy()
method, but I can't seem to make it work, it always returns null even though entities with the specific role I'm trying to find exist.
Here is my entity and its properties:
/**
* @ORM\Entity(repositoryClass=SalarieRepository::class)
*/
class Salarie
{
/**
* @ORM\Id
* @ORM\GeneratedValue
* @ORM\Column(type="integer")
*/
private $id;
/**
* @ORM\Column(type="string", length=255)
*/
private $nom;
/**
* @ORM\Column(type="string", length=255)
*/
private $prenom;
/**
* @ORM\Column(type="string", length=255)
*/
private $email;
/**
* @ORM\Column(type="string", length=255, nullable=true)
*/
private $telephone;
/**
* @ORM\Column(type="string", length=255)
*/
private $service;
/**
* @ORM\Column(type="json")
*/
private $roles = [];
// Getters & setters
}
And here is an example of something I tried with findOneBy()
inside a controller, that returns null
:
$rolecheck = $this->salarieRepository->findOneBy(["roles" => ["ROLE_RESPONSABLE_RH"]]);
When I try with any other property of the entity which isn't an array it works well, if I do something like this:
$rolecheck = $this->salarieRepository->findOneBy(["nom" => "test"]);
dd($rolecheck);
It will show the right entity :
SalarieController.php on line 47:
App\Entity\Salarie {#1501 ▼
-id: 6
-nom: "test"
-prenom: "test"
-email: "test@test.test"
-telephone: null
-service: "Graphisme"
-roles: array:3 [▼
0 => "ROLE_RESPONSABLE_RH"
1 => "ROLE_RESPONSABLE_SERVICE"
2 => "ROLE_SALARIE"
]
}
Where we can also see it does have the roles array with the role I'm trying to find inside it.
Any clues on how I could try to find one entity which has the specific role "ROLE_RESPONSABLE_RH"
?
Your $roles
property is of type json
, which means it is stored as this in your database:
["ROLE_RESPONSABLE_RH", "ROLE_RESPONSABLE_SERVICE", "ROLE_SALARIE"]
You need to ask Doctrine if the JSON array contains the role, but you can't do that with the findOneBy()
method.
When you hit the ORM limitations you can use a Native Query with ResultSetMapping. It allows you to write a pure SQL query using specific features of your DBMS but still get entity objects.
Create this method in your SalarieRepository
class:
public function findByRole(string $role): array
{
// The ResultSetMapping maps the SQL result to entities
$rsm = $this->createResultSetMappingBuilder('s');
$rawQuery = sprintf(
'SELECT %s
FROM salarie s
WHERE /* your WHERE clause depending on the DBMS */',
$rsm->generateSelectClause()
);
$query = $this->getEntityManager()->createNativeQuery($rawQuery, $rsm);
$query->setParameter('role', $role);
return $query->getResult();
}
Then you need to replace the comment I put in the WHERE
clause depending on the DBMS:
MariaDB - JSON_SEARCH():
SELECT %s
FROM salarie s
WHERE JSON_SEARCH(s.roles, 'one', :role) IS NOT NULL
MySQL - JSON_CONTAINS():
SELECT %s
FROM salarie s
WHERE JSON_CONTAINS(s.roles, :role, '$')
Warning: you must enclose the
role
parameter with double quotes:$query->setParameter('role', sprintf('"%s"', $role));
PostgreSQL - jsonb escaped "?" operator:
SELECT %s
FROM salarie s
WHERE s.roles::jsonb ?? :role
Warning: will require PHP 7.4+. See the RFC
这篇关于Symfony 按角色查找用户(JSON 数组 Doctrine 属性)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!