如何在 Rails 的 WHERE 子句中使用 ANY 而不是 IN? [英] How to use ANY instead of IN in a WHERE clause with Rails?

问题描述

我曾经有这样的查询:

MyModel.where(id: ids)

生成 sql 查询如下:

Which generates sql query like:

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

现在我想将其更改为使用 ANY 而不是 IN.我创建了这个:

Now I want to change this to use ANY instead of IN. I created this:

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

现在当我使用空数组 ids = [] 我得到以下错误:

Now when I use empty array ids = [] I get the folowing error:

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'

推荐答案

IN 表达式有两种变体:

• 表达式 IN(子查询)
• 表达式 IN(值[, ...])

类似地，具有 ANY 结构的两个变体:

Similarly, two variants with the ANY construct:

• 表达式运算符 ANY (子查询)
• 表达式运算符 ANY (数组表达式)

子查询适用于任何一种技术，但对于每种技术的第二种形式，IN需要一个值列表(如标准SQL) 而 = ANY 需要一个 数组.

A subquery works for either technique, but for the second form of each, IN expects a list of values (as defined in standard SQL) while = ANY expects an array.

ANY 是后来的，更通用的加法，它可以与任何返回布尔值的二元运算符结合使用.IN 分解为 ANY 的一个特例.其实它的第二种形式是内部改写的:

ANY is a later, more versatile addition, it can be combined with any binary operator returning a boolean value. IN burns down to a special case of ANY. In fact, its second form is rewritten internally:

IN 重写为 = ANY
NOT IN 改写为 <>全部

检查 EXPLAIN 输出以了解任何查询以亲自查看.这证明了两件事:

Check the EXPLAIN output for any query to see for yourself. This proves two things:

• IN 永远不会比 = ANY 快.
• = ANY 不会明显更快.

• IN can never be faster than = ANY.
• = ANY is not going to be substantially faster.

选择应该由什么更容易提供来决定:一个值列表或一个数组(可能作为数组文字 - 单个值).

The choice should be decided by what's easier to provide: a list of values or an array (possibly as array literal - a single value).

如果您要传递的 ID 来自数据库，那么直接选择它们(子查询)或使用 将源表集成到查询中会更有效率加入(如@mu 评论).

If the IDs you are going to pass come from within the DB anyway, it is much more efficient to select them directly (subquery) or integrate the source table into the query with a JOIN (like @mu commented).

要从您的客户传递长列表值并获得最佳性能，请使用数组, unnest() 并加入，或使用 VALUES 将其作为表表达式提供(例如 @PinnyM 评论).但请注意，JOIN 会在提供的数组/集合中保留可能的重复，而 IN 或 = ANY 则不会.更多:

To pass a long list of values from your client and get the best performance, use an array, unnest() and join, or provide it as table expression using VALUES (like @PinnyM commented). But note that a JOIN preserves possible duplicates in the provided array / set while IN or = ANY do not. More:

• 使用大 IN 优化 Postgres 查询

在存在 NULL 值的情况下，NOT IN 通常是错误的选择，而 NOT EXISTS 是正确的(而且速度更快):

In the presence of NULL values, NOT IN is often the wrong choice and NOT EXISTS would be right (and faster, too):

• 选择不存在于其他表

对于 Postgres 接受的数组表达式:

For the array expression Postgres accepts:

• an 数组构造函数(数组由 Postgres 端的值列表构成)的形式:ARRAY[1,2,3]
• 或数组文字 形式为 '{1,2,3}'.

为了避免无效的类型转换，您可以显式转换:

To avoid invalid type casts, you can cast explicitly:

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

相关:

• PostgreSQL:将数组传递给过程的问题
• 如何将自定义类型数组传递给Postgres 函数

或者你可以创建一个带有 VARIADIC 参数的 Postgres 函数，它接受单个参数并从它们形成一个数组:

Or you could create a Postgres function taking a VARIADIC parameter, which takes individual arguments and forms an array from them:

• 在单个参数中传递多个值

假设 id 为 integer:

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

但我只是涉足 Ruby.@mu 在此相关答案中提供了详细说明:

But I am just dabbling in Ruby. @mu provides detailed instructions in this related answer:

• 将值数组发送到ruby 中的 sql 查询?

这篇关于如何在 Rails 的 WHERE 子句中使用 ANY 而不是 IN?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！