Clash Royale CLAN TAG

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>


How can I get the JobRequirement using an age: 20?

JobRequirement

age: 20

Something like

JobRequirement.where(age: 20)


age

1 Answer
1

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)


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('col in (?)', 18..20)

where age <@ 18,19,20


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)"


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))


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

connection

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


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))


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

ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: operator does not exist: integer <@ integer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.