Как использовать ANY вместо IN в предложении WHERE с Rails?

У меня был запрос вроде:

MyModel.where(id: ids)

Что генерирует SQL-запрос, например:

SELECT "my_models".* FROM "my_models"
WHERE  "my_models"."id" IN (1, 28, 7, 8, 12)

Теперь я хочу изменить это, чтобы использовать ANY вместо IN. Я создал это:

MyModel.where("id = ANY(VALUES(#{ids.join '),('}))"

Теперь, когда я использую пустой массив ids = [], я получаю следующую ошибку:

MyModel Load (53.0ms)  SELECT "my_models".* FROM "my_models"  WHERE (id = ANY(VALUES()))
ActiveRecord::JDBCError: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
ActiveRecord::StatementInvalid: ActiveRecord::JDBCError: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
Position: 75: SELECT "social_messages".* FROM "social_messages"  WHERE (id = ANY(VALUES()))
    from arjdbc/jdbc/RubyJdbcConnection.java:838:in `execute_query'

Ответ 1

Существует два варианта выражений IN:

Аналогично, два варианта с конструкцией ANY:

Подзапрос работает для любого метода, но для второй формы каждого из них IN ожидает список значений (как определено в стандартном SQL), в то время как = ANY ожидает массив.

Что использовать?

ANY является более поздним, более универсальным дополнением, его можно комбинировать с любым двоичным оператором, возвращающим логическое значение. IN сгорает до особого случая ANY. Фактически, его вторая форма переписана внутри:

IN переписывается с = ANY
NOT IN переписывается с <> ALL

Проверьте вывод EXPLAIN для любого запроса, чтобы убедиться в этом. Это доказывает две вещи:

IN никогда не может быть быстрее, чем = ANY.
= ANY не будет существенно быстрее.

Выбор должен быть решен с помощью того, что проще предоставить: списка значений или массива (возможно, в качестве литерала массива - одного значения).

Если идентификаторы, которые вы собираетесь передать, приходят из БД в любом случае, гораздо эффективнее выбирать их напрямую (подзапрос) или интегрировать исходную таблицу в запрос с помощью JOIN (например, @mu прокомментировал).

Чтобы передать длинный список значений из вашего клиента и получить лучшую производительность, используйте массив unnest() и join или предоставьте его в виде табличного выражения, используя VALUES (например, @PinnyM прокомментировал). Но обратите внимание, что JOIN сохраняет возможные дубликаты в предоставленном массиве/наборе, в то время как IN или = ANY нет. Подробнее:

Оптимизация запроса Postgres с большим IN

При наличии значений NULL NOT IN часто является неправильным выбором, и NOT EXISTS будет правильным (и тоже быстрее):

Выберите строки, которых нет в другой таблице

Синтаксис для `= ANY`

Для выражения массива Postgres принимает:

конструктор массива (массив создается из списка значений на стороне Postgres) в форме: ARRAY[1,2,3]
или литерал массива в форме '{1,2,3}'.

Чтобы избежать приведения недопустимых типов, вы можете привести явное приведение:

ARRAY[1,2,3]::numeric[]
'{1,2,3}'::bigint[]

Связанный:

Или вы можете создать функцию Postgres, принимающую параметр VARIADIC, который принимает отдельные аргументы и формирует из них массив:

Передача нескольких значений в одном параметре

Как передать массив из Ruby?

Предполагая, что id будет integer:

MyModel.where('id = ANY(ARRAY[?]::int[])', ids.map { |i| i})

Но я просто балуюсь Руби. @mu предоставляет подробные инструкции в этом связанном ответе:

Отправка массива значений в SQL-запрос в ruby?

Ответ 1

Что использовать?

Синтаксис для = ANY

Как передать массив из Ruby?

Синтаксис для `= ANY`