Activerecord query .where string as number

Question

I have record in my db where there is a time element which is a number stored as a string. I'd like to run a where query where I check if the number is greater than x e.g.

Recording.where("recording_duration_ms.to_i > 60000")

Sure it must be simple to do...

Why is recording_duration_ms stored as a string? Wouldn't it make more sense to fix your schema rather than kludging around it? — mu is too short
– mu is too short, Commented Nov 5, 2015 at 18:43
Going with @muistooshort, you could write a migration task to get all values, change column type (drop and add), then insert the strings as ints. I wouldn't be surprised if has larger implications than you are seeing now. From there you can use Client.where("recording_duration_to_ms > ?", 60000) — Joe Essey
– Joe Essey, Commented Nov 5, 2015 at 19:12
Have some sympathy with the comments above. It just happened that the return from an external API has the value as a string, so I'd have to be aware that I'd changed the data type if I was cross checking against the values returned by the API. Which I don't suppose is impossible,just a bit annoying. — Carpela
– Carpela, Commented Nov 18, 2015 at 23:26

Kalman · Accepted Answer · 2015-11-05 19:04:08Z

7

Perfecting the answer that @Alex Peachey gave...

To avoid SQL injection, you want to use either ? or hash

Recording.where("CAST(recording_duration_ms AS INT) > ?", 60000)

or

Recording.where("CAST(recording_duration_ms AS INT) > :duration", duration: 60000)

edited Nov 5, 2015 at 19:04

answered Nov 5, 2015 at 18:48

Kalman

8,1311 gold badge29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hendrik Over a year ago

You are right on the SQL injection part. Never put your variables into the query unless you are 100% sure.

Alex Peachey Over a year ago

While it's good to avoid SQL injection when building your queries, there was no injection possibility because the 60000 was not variable. If it was variable and being interpolated, then yes definitely avoid injection.

Kalman Over a year ago

"check if the number is greater than x" in the question sounded like a variable, but I guess you are correct - the example did not contain a variable. In any case though, hardcoded queries are boring. :) Today it's 60000 and tomorrow it's something else. The code should be able to handle passing in a parameter for the value to query against.

Alex Peachey · Accepted Answer · 2015-11-05 18:33:31Z

4

Most SQL databases allow you to CAST values to different data types. The query most likely to do what you want is:

Recording.where('CAST(recording_duration_ms AS INT) > 60000')

answered Nov 5, 2015 at 18:33

Alex Peachey

4,68625 silver badges18 bronze badges

Comments

Dmitry Morozov · Accepted Answer · 2015-11-05 19:15:41Z

0

This works with the PostgreSQL, at least

Recording.where('recording_duration_ms::int > ?', 60000)

answered Nov 5, 2015 at 19:15

Dmitry Morozov

112 bronze badges

Comments

nialbima · Accepted Answer · 2015-11-05 18:25:02Z

-2

PSQL stores everything as a string, so you need single quotes around 60000:

Recording.where("recording_duration_ms.to_i > '60000'")

(I think)

answered Nov 5, 2015 at 18:25

nialbima

313 bronze badges

1 Comment

mu is too short Over a year ago

(1) PostgreSQL does not store everything as strings. (2) .to_i is a Ruby method call, not SQL. (3) String comparisons don't work like numeric comparisons, '2' > '10' for example is true.

Collectives™ on Stack Overflow

Activerecord query .where string as number

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related