如何使用在一个WHERE使用Rails条款有何呢? [英] How to use ANY instead of IN in a WHERE clause with Rails?
问题描述
我以前也有类似的查询:
MyModel.where(ID:IDS)
生成SQL查询,如:
选择my_models。* FROMmy_models
WHEREmy_models。IDIN(1,28,7,8,12)
现在我想改变这种使用任何
,而不是的IN
。我创造了这个:
MyModel.where(ID = ANY(VALUES(#{ids.join'),('}))
现在,当我使用空数组的id = []
我得到了如下因素的错误:
为MyModel负荷(53.0ms)选择my_models。* FROMmy_modelsWHERE(ID = ANY(VALUES()))
ActiveRecord的:: JDBCError:org.postgresql.util.PSQLException:错误:语法错误或接近)
ActiveRecord的:: StatementInvalid:ActiveRecord的:: JDBCError:org.postgresql.util.PSQLException:错误:在语法错误或接近)
位置:75:选择social_messages* FROMsocial_messagesWHERE(ID = ANY(VALUES()))
从arjdbc / JDBC / RubyJdbcConnection.java:838:在'execute_query
有两种变体的IN
EX pressions:
- <一个href="http://www.postgresql.org/docs/current/interactive/functions-subquery.html#FUNCTIONS-SUBQUERY-IN"相对=nofollow>
EX pression IN(子查询)
-
EX pression IN (值[,...])
同样,两个变体与任何
结构:
- <一个href="http://www.postgresql.org/docs/current/interactive/functions-subquery.html#FUNCTIONS-SUBQUERY-ANY-SOME"相对=nofollow> EX pression运营商ANY(子查询)
- EX pression操作员的(阵列前pression)
一个子查询的作品为,但在第二形式的每一:在
预计值的列表 (在标准SQL定义的),而 = ANY
期待一个阵列
哪些使用?
都做同样的。 任何
是后来,更灵活另外,它还可以与各种运营商的结合。 在
火烧到的特殊情况下,任何
。事实上,它的第二种形式在内部重写任何
:
在
改写为 = ANY
。在
改写为&LT;&GT;所有
检查 EXPLAIN
输出任何查询,看到自己。这证明了两件事:
-
在
,并且不能超过= ANY
。 更快
-
= ANY
不会被大大加快。
的选择应以决定的什么是更方便地提供:值的列表或数组(单值)
如果你要的ID传递过来的从DB 无论如何,这是更有效的直接(子查询)选择它们或源表与<$ C $融入查询C> JOIN (如<一个href="http://stackoverflow.com/questions/31191507/how-to-use-any-in-where-clause-with-rails-4-0-instead-of-in/31192557#comment50389756_31191507">@mu评论)。
在NULL值的presence,。在
往往是错误的选择和 NOT EXISTS
会是正确的(快,太):
- <一个href="http://stackoverflow.com/questions/19363481/select-rows-which-are-not-$p$psent-in-other-table/19364694#19364694">Select这是不是present在其他表 行
语法 = ANY
对于数组前pression的Postgres接受:
- 的<一个href="http://www.postgresql.org/docs/current/interactive/sql-ex$p$pssions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS"
ARRAY [1,2,3:相对=nofollow> 数组构造 形式(数组从Postgres的侧值列表构造) ]
- 或 数组文本 形式
'{1,2,3}
。
要避免无效的类型转换,你可以显式转换:
ARRAY [1,2,3] ::数字[]
{1,2,3}':: BIGINT []
相关报道:
- <一个href="http://stackoverflow.com/questions/27963380/postgresql-issue-with-passing-array-to-procedure/27963713#27963713">PostgreSQL:问题传球达阵,以程序
- <一个href="http://stackoverflow.com/questions/12009822/how-to-pass-custom-type-array-to-postgres-function/17840058#17840058">How通过自定义类型数组Postgres的功能
或者你的可以的创建一个Postgres函数取可变参数
参数,这需要个人的观点,并形成一个数组从他们:
- <一个href="http://stackoverflow.com/questions/28109037/passing-multiple-values-in-single-parameter/28115702#28115702">Passing单参数的多个值
如何从红宝石传递数组?
假设 ID
是整数
:
MyModel.where('ID = ANY(ARRAY :: INT [])[?],ids.map {| I | I})
不过,我只是涉足红宝石。 @mu提供此相关的答案的详细说明:
- <一个href="http://stackoverflow.com/questions/22461042/sending-array-of-values-to-a-sql-query-in-ruby/22462519#22462519">Sending值的数组中的红宝石SQL查询?
I used to have a query like:
MyModel.where(id: ids)
Which generates sql query like:
SELECT "my_models".* FROM "my_models"
WHERE "my_models"."id" IN (1, 28, 7, 8, 12)
Now I want to change this to use ANY
instead of IN
. I created this:
MyModel.where("id = ANY(VALUES(#{ids.join '),('}))"
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'
There are two variant of IN
expressions:
Similarly, two variants with the ANY
construct:
A subquery works for both, but for the second form of each: IN
expects a list of values (as defined in standard SQL) while = ANY
expects an array.
Which to use?
Both do the same. ANY
is a later, more versatile addition, it can be combined with a variety of operators. IN
burns down to a special case of ANY
. In fact, its second form is rewritten internally with ANY
:
IN
is rewritten with = ANY
NOT IN
is rewritten with <> ALL
Check the EXPLAIN
output for any query to see for yourself. This proves two things:
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 (single value).
If the ids you are going to pass come from 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).
In the presence of NULL values, NOT IN
is often the wrong choice and NOT EXISTS
would be right (and faster, too):
Syntax for = ANY
For the array expression Postgres accepts:
- an array constructor (array is constructed from a list of values on the Postgres side) of the form:
ARRAY[1,2,3]
- or an array literal of the form
'{1,2,3}'
.
To avoid invalid type casts, you can cast explicitly:
ARRAY[1,2,3]::numeric[]
'{1,2,3}'::bigint[]
Related:
- PostgreSQL: Issue with passing array to procedure
- How to pass custom type array to Postgres function
Or you could create a Postgres function taking a VARIADIC
parameter, which takes individual arguments and forms an array from them:
How to pass the array from Ruby?
Assuming id
to be integer
:
MyModel.where('id = ANY(ARRAY[?]::int[])', ids.map { |i| i})
But I am just dabbling in Ruby. @mu provides detailed instructions in this related answer:
这篇关于如何使用在一个WHERE使用Rails条款有何呢?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!