Postgresql中的教义JSON列存储相同数据的对象或数组 [英] doctrine JSON column in postgresql is storing objects or arrays of the same data

问题描述

我有一个Symfony应用程序， User [doctrine]实体具有属性 roles ，它应该是数组或字符串.

I have a Symfony application, the User [doctrine] entity has the property roles, it should be an array or strings.

    /**
     * @ORM\Column(type="json")
     */
    private $roles = [];

    public function getRoles(): array
    {
        $roles = $this->roles;
        // guarantee every user at least has ROLE_USER
        $roles[] = 'ROLE_USER';

        return array_unique($roles);
    }

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

        return $this;
    }

很久以前，但是我很确定这部分(上面的代码)是我根据

It was a long time ago, but I am pretty sure this part (code above) is what I got with php bin/console make:user as per the documentation. As you can see, it always is an array.

我的应用程序已经达到需要根据角色选择 Users 的地步，但是我遇到的问题是数据库数据不一致.

I have reached a point in my app where I need to select Users based on a role, but the problem I am faicing is that the db data is Inconsistent.

["ROLE_TRANSLATOR","ROLE_DATA_ENTRY"]
["ROLE_TRANSLATOR","ROLE_DATA_ENTRY","ROLE_SITE_DIRECTOR"]
["ROLE_DATA_ENTRY"]
[]
{"0":"ROLE_SITE_DIRECTOR","2":"ROLE_TRANSLATOR","1":"ROLE_DATA_ENTRY"}
["ROLE_SPONSOR_REP"]
["ROLE_TRANSLATOR","ROLE_DATA_ENTRY"]
{"0":"ROLE_SPONSOR_REP","2":"ROLE_TRANSLATOR","1":"ROLE_DATA_ENTRY","3":"ROLE_SITE_DIRECTOR"}

结果，如果我要查找 ROLE_TRANSLATOR ，我必须检入一个数组或每个对象键

As a result, if I am looking for ROLE_TRANSLATOR I have to check in an array, or every object key

WHERE u.roles::jsonb ? 'ROLE_TRANSLATOR'
    OR u.roles::json ->> '0' = 'ROLE_TRANSLATOR'
    OR u.roles::json ->> '1' = 'ROLE_TRANSLATOR'
    OR u.roles::json ->> '2' = 'ROLE_TRANSLATOR'
    OR u.roles::json ->> '3' = 'ROLE_TRANSLATOR'

随着我添加更多角色，情况只会变得更糟.目前只有4.

This will only get worse as I add more roles. Currently there are only 4.

我的问题；
为什么学说不一致?而且，我可以修复它吗?或
如何制作更简单的WHERE子句?

My Questions;
Why is doctrine being Inconsistent? And, can I fix it? OR
How can I make a simpler WHERE clause?

在其余的应用中，角色和安全性都按预期工作.

as far as the rest of the aplication goes, roles and security works as expected.

推荐答案

Slack上@Dave Redfern的疯狂道具指出了我的问题.传递非零索引数组时，它会作为对象插入.

Mad props to @Dave Redfern on Slack who pointed out my problem. When passing a non-zero indexed array, it is interperted as an object.

dump(json_encode([
    0 => "ROLE_SITE_DIRECTOR", 2 => "ROLE_TRANSLATOR", 1 => "ROLE_DATA_ENTRY",
]));
dump(json_encode(array_values([
    0 => "ROLE_SITE_DIRECTOR", 2 => "ROLE_TRANSLATOR", 1 => "ROLE_DATA_ENTRY",
])));

输出将是:

"{"0":"ROLE_SITE_DIRECTOR","2":"ROLE_TRANSLATOR","1":"ROLE_DATA_ENTRY"}"
"["ROLE_SITE_DIRECTOR","ROLE_TRANSLATOR","ROLE_DATA_ENTRY"]"

所以修复很简单，我是二传手:

So the fix is simple, im my setter:

$this->roles = array_values($roles);

继续前进，对角色使用数据库关系也是一种更好的做法.通过JSON数据进行搜索并不是一件很有趣的事情，但在联接中却是标准做法.

Moving forward, it is also a better practice to use database relationships to the roles. Searching by JSON data is not fun, but in a join is standard practice.

这篇关于Postgresql中的教义JSON列存储相同数据的对象或数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！