ruby-on-rails - 应该使用什么 postgres 运算符来比较数组/整数与数组？

Question

我有一个 Event 模型，其中有一个用于 visibility_constraints 的 jsonb 列。一条记录通常包含四个数组included_users、included_teams、excluded_users 和excluded_teams。 Event 具有公开或私有(private)的可见性。

还为四个数组创建了索引:

CREATE INDEX visibility_constraints_included_teams_on_events ON events USING GIN ((visibility_constraints->'included_teams'))

创建事件

Event.create(summary: 'Test', visibility_constraints: { included_users: [1, 2, 3], included_teams: [1, 2], excluded_users: [4, 5, 6], excluded_teams: [3, 4] })

=> #<Event:0x00007fc99d8f9670
 id: 1,
 summary: "Test",
 starts_at: Sat, 11 May 2019 21:00:00 CEST +02:00,
 ends_at: Sat, 11 May 2019 22:00:00 CEST +02:00,
 visibility: "vis_private",
 visibility_constraints: {"included_users"=>[1, 2, 3], "included_teams"=>[1, 2], "excluded_users"=>[4, 5, 6], "excluded_teams"=>[3, 4]}>

现在我尝试检索 current_user.id 在 included_users 中的事件……

Event.where("visibility_constraints -> 'included_users' ? :user_id", user_id: current_user.id.to_json)

(0.4ms)  SELECT "events".* FROM "events" WHERE (visibility_constraints -> 'included_users' ? '1')

... 或者 current_user.team_ids 在 included_teams 中的位置。

Event.where("visibility_constraints -> 'included_teams' ? :team_ids", team_ids: current_user.team_ids.to_json)

 (0.5ms)  SELECT "events".* FROM "events" WHERE (visibility_constraints -> 'included_teams' ? '[3,5,1]')

这些查询总是不返回任何内容，所以我想知道我可能做错了什么。我忘记了什么地方吗？使用不同的运算符(好吧，我试过了，但没有任何变化)？

感谢您的帮助!

---

编辑

\d 公共(public)事件

+------------------------+-----------------------------+------------------------------------------------------+
| Column                 | Type                        | Modifiers                                            |
|------------------------+-----------------------------+------------------------------------------------------|
| id                     | bigint                      |  not null default nextval('events_id_seq'::regclass) |
| summary                | character varying           |                                                      |
| description            | text                        |                                                      |
| starts_at              | timestamp without time zone |                                                      |
| ends_at                | timestamp without time zone |                                                      |
| allday                 | boolean                     |                                                      |
| created_at             | timestamp without time zone |  not null                                            |
| updated_at             | timestamp without time zone |  not null                                            |
| aasm_state             | character varying           |                                                      |
| visibility             | visibility                  |  default 'vis_private'::visibility                   |
| stage_id               | bigint                      |                                                      |
| visibility_constraints | jsonb                       |  not null default '{}'::jsonb                        |
+------------------------+-----------------------------+------------------------------------------------------+
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)
    "index_events_on_stage_id" btree (stage_id)
    "index_events_on_visibility_constraints" gin (visibility_constraints)
    "visibility_constraints_included_teams_on_events" gin ((visibility_constraints -> 'included_teams'::text))
    "visibility_constraints_included_users_on_events" gin ((visibility_constraints -> 'included_users'::text))
Foreign-key constraints:
    "fk_rails_cb489a462e" FOREIGN KEY (stage_id) REFERENCES stages(id)
Referenced by:
    TABLE "availabilities" CONSTRAINT "fk_rails_6d29de0e36" FOREIGN KEY (event_id) REFERENCES events(id)
    TABLE "assignments" CONSTRAINT "fk_rails_6a5328b71e" FOREIGN KEY (event_id) REFERENCES events(id)
    TABLE "resource_allocations" CONSTRAINT "fk_rails_2958f606d4" FOREIGN KEY (event_id) REFERENCES events(id)

Time: 0.048s

---

编辑 2

SELECT * FROM 事件

+------+-----------+----------------------------+----------------------------+-----------------------------------------------------------------------------------------------------------------+
| id   | summary   | created_at                 | updated_at                 | visibility_constraints                                                                                          |
|------+-----------+----------------------------+----------------------------+-----------------------------------------------------------------------------------------------------------------|
| 3    | event 3   | 2019-05-15 00:24:50.699237 | 2019-05-15 00:24:50.699237 | "{\"included_users\":[1,2,3],\"included_teams\":[1,2,3],\"excluded_users\":[4,5,6],\"excluded_teams\":[4,5,6]}" |
| 4    | event 4   | 2019-05-15 00:24:55.487215 | 2019-05-15 00:24:55.487215 | "{\"included_users\":[1,2,3],\"included_teams\":[1,2,3],\"excluded_users\":[4,5,6],\"excluded_teams\":[4,5,6]}" |
| 5    | event 5   | 2019-05-15 00:26:15.872433 | 2019-05-15 00:26:15.872433 | "{\"included_users\":[2,3],\"included_teams\":[],\"excluded_users\":[],\"excluded_teams\":[]}"                  |
+------+-----------+----------------------------+----------------------------+-----------------------------------------------------------------------------------------------------------------+

Answer 1

将 user_id 绑定(bind)到关键字参数对您的情况没有多大帮助。您可以做的是将其包装在 [] 中并将其转换为 JSON。

使用“包含”运算符，您可以检查左边的 JSON 值是否在顶层包含正确的 JSON 路径/值条目？:

Event.where(
  'visibility_constraints @> ?', { included_users: [current_user.id] }.to_json
)

生成:

SELECT "events".*
FROM "events"
WHERE (visibility_constraints @> '{"included_users":[<current_user_id_as_integer>]}')