Как использовать ЛЮБОЙ вместо IN в предложении WHERE с Rails?

Question

Раньше у меня был такой запрос:

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'

Answer 1

Есть два варианта IN выражения:

• expression IN (subquery)
• expression IN (value [, ...])

Аналогично, два варианта с ANY построить:

• expression operator ANY (subquery)
• expression operator ANY (array expression)

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

Какой использовать?

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

IN переписан с = ANY
NOT IN переписан с <> ALL

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

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

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

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

Чтобы передать длинный список значений от вашего клиента и получить лучшую производительность, используйте массив, unnest() и присоединиться, или предоставить его как табличное выражение, используя VALUES (как @PinnyM прокомментировал). Больше:

• Оптимизация запроса 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[]

Связанные с:

• PostgreSQL: проблема с передачей массива в процедуру
• Как передать массив пользовательских типов в функцию Postgres

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

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

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

Если предположить, id быть integer:

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

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

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