Запрос к типу столбца массива Postgres

Question

TL;DR Мне интересно, какие плюсы и минусы (или если они даже эквивалентны) между @> {as_champion, whatever} и используя IN ('as_champion', 'whatever') является. Подробности ниже:

Я работаю с Rails и использую тип столбца Postgres для массива, но мне нужно использовать raw sql для моего запроса, так как методы поиска Rails не очень хорошо с ним работают. Я нашел способ, который работает, но интересно, каков предпочтительный метод:

roles колонка на Memberships таблица мой столбец массива. Это было добавлено через рельсы как так:

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

Когда я рассматриваю таблицу, она показывает тип как: text[] (не уверен, действительно ли Postgres представляет столбец массива или это Rails shenanigans.

Чтобы сделать запрос, я делаю что-то вроде:

Membership.where("roles @> ?", '{as_champion, whatever}')

Answer 1

Из прекрасного руководства по эксплуатации Array:

Оператор: @>
Описание: содержит
Пример: ARRAY[1,4,3] @> ARRAY[3,1]
Результат: t (Ака правда)

Так @> обрабатывает свои массивы операндов как наборы и проверяет, является ли правая сторона подмножеством левой стороны.

IN немного отличается и используется с подзапросами:

9.22.2. В

expression IN (subquery)

Правая часть - это вложенный в скобки подзапрос, который должен возвращать ровно один столбец. Левое выражение оценивается и сравнивается с каждой строкой результата подзапроса. Результат IN равно "true", если найдена какая-либо равная строка подзапроса. Результатом является "false", если не найдено ни одной равной строки (включая случай, когда подзапрос не возвращает строк).

или с буквенными списками:

9.23.1. В

expression IN (value [, ...])

Правая часть представляет собой скобочный список скалярных выражений. Результат "true", если результат левого выражения равен любому из правых выражений. Это сокращенное обозначение для

expression = value1
OR
expression = value2
OR
...

Так a IN b более или менее означает:

Является ли значение a равно любому из значений в списке 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)

и все будет работать как положено. Вы по-прежнему работаете с небольшими фрагментами SQL (поскольку ActiveRecord не имеет полного представления о массивах SQL или любом способе представления @> оператор), но, по крайней мере, вам не придется беспокоиться о проблемах с цитированием. Вы могли бы, вероятно, пройти через AREL, чтобы вручную добавить @> поддержка, но я считаю, что AREL быстро превращается в непонятный и нечитаемый беспорядок для всех, кроме самых тривиальных целей.