sql - RoR model.where() 使用 pg 数组类型

Question

我偶然想到了通过在我的模型中使用数组来限制我的表和关联的想法 like so .

我正在开发一个任务分配应用程序，用户基本上会构造一个句子来执行一个 Action 。我使用关键字来帮助构建句子的边界。

示例包括(括号中的关键字):

• [I will] paint the fence"<- 创建一个新任务，分配给 current_user
• [请求] Huck [去] 粉刷围栏” <- find_or_create 任务，分配给 find_or_create 用户
• [存档] 粉刷围栏” <- 软删除任务

这是我的迁移:

class CreateKeywords < ActiveRecord::Migration[5.1]
  def change
    create_table :keywords do |t|
      t.string :name, null: false
      t.text :pre, array: true, default: []
      t.text :post, array: true, default: []
      t.string :method, null: false, default: "read" # a CRUD indicator
    end
  end
end

keyword.post 表示什么模型可以跟在关键字后面

keyword.pre 表示哪些模型可以放在关键字之前

我的种子数据是这样的:

Keyword.create([
  { name: "i will", post: ["task"] },
  { name: "ask", post: ["user"] },
  { name: "assign", post: ["user", "task"] },
  { name: "find a", post: ["user", "task"] },
  { name: "make a new", post: ["user", "task"], method: "create" },
  { name: "finalize", post: ["task"] },
  { name: "archive", post: ["user", "task"], method: "delete" },
  { name: "update", post: ["user", "task"], method: "update" },
  { name: "for", post: ["user", "task"], pre: ["user", "task"] },
  { name: "to", post: ["user", "task"], pre: ["user", "task"] },
  { name: "and repeat", pre: ["task"] },
  { name: "before", pre: ["task"] },
  { name: "after", pre: ["task"] },
  { name: "on", pre: ["task"] }
])

现在我想做这样的事情:

key = Keyword.third

Keyword.where(pre: key.post)

但这会返回完全匹配，我想做类似的事情:

“返回可以在 Keyword.pre 中找到 key.post 的任何值的所有关键字”

我在这些方面没有任何运气:Keyword.where(pre.include?key.post)

我可以遍历所有关键字并使用 AND:

results = []
Keyword.all.each do |k|
  comb = k.pre & key.post
  if comb.present?
    results << k
  end
end
results.map { |k| k.name }

但这感觉很糟糕。

而且我对执行此操作所需的 SQL 有点不知所措。

有什么建议吗？

Answer 1

有两件事你想知道:

1. PostgreSQL 的 "array constructor" syntax看起来像 array['a', 'b', 'c']而不是更常见的'{a,b,c}'语法。
2. PostgreSQL 的 array operators .

数组构造函数语法很方便，因为当 ActiveRecord 将数组视为占位符的值时，它会将数组扩展为逗号分隔列表，这与 x in (?) 一样有效。和 x && array[?] .

要让运算符(operator)使用，您需要:

all keywords where any value of key.post can be found in Keyword.pre

这是 key.post 的另一种说法和 Keyword.pre重叠，运算符是 && .如果您需要稍微不同的逻辑，还有子集 (<@) 和超集 (@>) 运算符。

综合起来:

Keyword.where('pre && array[?]', key.post)

或:

Keyword.where('pre && array[:post]', post: key.post)

lacostenycoder ，在评论中，关注空数组是正确的。 ActiveRecord 将空数组扩展为单个 null填写占位符时，您最终可能会像这样执行 SQL:

pre && array[null]

并且 PostgreSQL 将无法确定 array[null] 的类型.你可以包括类型转换:

Keyword.where('pre && array[:post]::text[]', post: key.post)
# --------------------------------^^^^^^^^ tell PostgreSQL that this is an array of text

但是，由于 pre && array[null]::text[]永远不会是真的，如果key.post.empty?，你可以跳过整个事情.

空数组不与任何其他数组重叠(甚至不与另一个空数组重叠)，因此您无需担心超出 ActiveRecord 处理范围的空数组。同样对于 pre is null , null && any_array永远不会是真的(它实际上会评估为 null )所以不会与这些事情有任何重叠。