如何检查数字是否在activerecord的范围内？ [英] How to check if a number is within range in activerecord?

问题描述

我有一个带范围列的模型

I have a model with a range column

#<JobRequirement id: 1, age: 18...30>

如何使用 b获取工作要求 年龄：20 ？

类似

JobRequirement.where(age: 20)

推荐答案

我认为您需要使用 PostgreSQL范围运算符和一个字符串中的一些SQL。特别是，您希望 @> （包含元素）运算符：

I think you need to use the PostgreSQL range operators and a bit of SQL in a string. In particular, you'd want the @> (contains element) operator:

JobRequirement.where('age @> ?', 20)

根据占位符的值来提供范围：

As far as supplying a range as a placeholder value goes:

JobRequirement.where('age <@ ?', 18..20)

您会发现AR对PostgreSQL范围类型的了解有些有限。当您提供一个范围作为占位符的值时，AR会希望将范围扩展到以逗号分隔的列表，因为它假设您说的是 where（'col in（？）' 18..20），因此您最终会胡说八道：

you'll find that AR's knowledge of PostgreSQL's range types is somewhat limited. When you supply a range as a value for a placeholder, AR will want to expand the range to a comma delimited list as it assumes that you're saying something like where('col in (?)', 18..20) so you end up with nonsense like:

where age <@ 18,19,20

在生成的SQL中。您可以通过手动键入强制值来解决此问题。例如：

in the generated SQL. You can get around this by manually type casting the value; for example:

> ActiveRecord::Base.connection.type_cast(6..11)
 => "[6,11]" 
> ActiveRecord::Base.connection.type_cast(6...11)
 => "[6,11)" 

然后将字符串发送到查询中，PostgreSQL应该将其转换为PostgreSQL范围自动：

and then sending the string into the query where PostgreSQL should cast it to a PostgreSQL-range automatically:

JobRequirement.where('age <@ ?', ActiveRecord::Base.connection.type_cast(18..20))

根据您在哪里执行操作， connection 方法可能适用于所有噪音：

Depending on where you're doing this, the connection method might be available with all the noise:

JobRequirement.where('age <@ ?', connection.type_cast(18..20))

如果PostgreSQL不是自己选择< @ 运算符的正确版本，然后可以通过更多类型转换帮助它：

And if PostgreSQL isn't picking the right version of the <@ operator on its own then you can help it with more typecasting:

JobRequirement.where('age <@ ?::int4range', connection.type_cast(18..20))

这篇关于如何检查数字是否在activerecord的范围内？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！