首页 文章

查询Postgres数组列类型

提问于
浏览 次
1

TL;DR 我想知道 @> {as_champion, whatever} 和使用 IN ('as_champion', 'whatever') 之间的利弊是什么（或者它们是否相等） . 详情如下：

我正在使用Rails并使用Postgres的数组列类型，但由于Rails finder方法不能很好地使用它，因此必须使用原始sql作为我的查询 . 我发现了一种有效的方法，但想知道首选方法是什么：

Memberships 表上的 roles 列是我的数组列 . 它是通过rails添加的，因为：

add_column :memberships, :roles, :text, array: true

当我检查表时，它将类型显示为： text[] （不确定这是否真的是Postgres如何表示数组列，或者是否是Rails shenanigans .

要查询它我做的事情如下：

Membership.where("roles @> ?", '{as_champion, whatever}')
ruby-on-rails postgresql ruby-on-rails-4 rails-activerecord array-column

1 回答

  • 3

    从罚款Array Operators manual：

    运算符：@>描述：包含示例：ARRAY [1,4,3] @> ARRAY [3,1]结果：t（AKA true）

    因此 @> 将其操作数数组视为集合并检查右侧是否是左侧的子集 .

    IN 有点不同，与subqueries一起使用：

    9.22.2 . IN表达式IN（子查询）
    右侧是带括号的子查询，它必须返回一列 . 评估左侧表达式并将其与子查询结果的每一行进行比较 . 如果找到任何相等的子查询行，则IN的结果为“true” . 如果找不到相等的行，则结果为“false”（包括子查询不返回任何行的情况） .

    或literal lists：

    9.23.1 . IN表达式IN（值[，...]）
    右侧是带括号的标量表达式列表 . 如果左侧表达式的结果等于任何右侧表达式，则结果为“true” . 这是expression = value1的简写表示法
    要么
    expression = value2
    要么
    ...

    所以 a IN b 或多或少意味着：

    值是否等于列表b中的任何值（可以是生成单个元素行或文字列表的查询） .

    当然，你可以这样说：

    array[1] in (select some_array from ...)
array[1] in (array[1], array[2,3])

    但是那些情况下的数组仍然被视为单个值（恰好有一些内部结构） .

    如果要检查数组是否包含任何值列表，则 @> 不是您想要的 . 考虑一下：

    array[1,2] @> array[2,4]

    4 不在 array[1,2] 中，因此 array[2,4] 不是 array[1,2] 的子集 .

    如果你想检查某人是否同时拥有这两个角色：

    roles @> array['as_champion', 'whatever']

    是正确的表达式但是如果要检查 roles 是否是这些值中的任何一个，那么您需要重叠运算符（ && ）：

    roles && array['as_champion', 'whatever']

    请注意，我正在使用各种数组的“数组构造函数”语法，这是因为使用工具（如ActiveRecord）更方便，它知道在替换占位符时将数组扩展为逗号分隔列表但是不完全理解SQL数组 .

    鉴于这一切，我们可以说：

    Membership.where('roles @> array[?]', %w[as_champion whatever])
Membership.where('roles @> array[:roles]', :roles => some_ruby_array_of_strings)

    一切都会按预期工作 . 您're still working with little SQL snippets (as ActiveRecord doesn' t完全了解SQL数组或任何表示 @> 运算符的方式）但至少您不必担心引用问题 . 你可以通过AREL手动添加 @> 支持，但我发现AREL很快就会陷入难以理解和难以理解的混乱中，除了最微不足道的用途 .

    回复于

相关问题