如何在Rails的WHERE子句中使用ANY而不是IN？

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 不会大大加快。

选择应该由更容易提供的内容决定：一个值列表或一个数组（可能作为数组文字-一个值）。

如果您要传递的ID仍然来自数据库内部，则直接选择它们（子查询）或将源表集成到查询中JOIN（例如@mu commented）会更加有效。

要通过一个长长的清单从客户价值并获得最佳的性能，使用一个数组，unnest()并加入，或使用提供它作为表表达式VALUES（如@PinnyM评论）。但是请注意，a JOIN可以或不可以在提供的数组/集中保留可能的重复项。更多：IN= ANY

使用大IN优化Postgres查询

在存在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函数

或者，您可以创建一个带VARIADIC参数的Postgres函数，该函数接受单个参数并从中形成一个数组：

在单个参数中传递多个值

如何从Ruby传递数组？

假设id是integer：

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

Answer 2

您的解决方案工作完美，除了当我使用空数组时，我得到：ActiveRecord :: StatementInvalid（ActiveRecord :: JDBCError：org.postgresql.util.PSQLException：错误：运算符不存在：integer = text提示：没有运算符与给定名称匹配，并且参数类型，您可能需要添加显式类型强制转换 

Answer 3

考虑到OP试图通过使用值列表而不是数组来提高性能（假设上述注释中的链接文章仍然正确），答案应使用格式VALUES (...), (...), ...而不是ARRAY[...]。还是我错过了什么？而且，如果该文章不再正确（或从未如此），那么此练习似乎没有任何意义……