2

Given a decimal column, is there a nice way to query for approximate equality?

You can use ranges:

Purchase.where(total: (value - delta)..(value + delta)).first

But it looks like a common enough issue that Rails would usually try to solve for you, given that floating numbers can't always be represented exactly.

ndnenkov
  • 35,425
  • 9
  • 72
  • 104
  • The floating issue turned to be [not so obvious](http://stackoverflow.com/questions/40807770/weird-rounding-issue). – ndnenkov Nov 25 '16 at 15:03

2 Answers2

1

I'm not sure if there is a gem that lets you do this cleanly in active record, but instead of using ranges, have you tried using SQL rounding? Something like:

Purchase.where('ROUND(total, 2) = ?', 30.67)

See this answer. Also the link therein to the docs. Finally, consider storing cents in the database like in this answer and then just changing them for display, thus avoiding dealing with floating point so much.

Community
  • 1
  • 1
mlabarca
  • 796
  • 7
  • 16
  • Float values are evil and will always lead to migraines. Coerce it into an integer or use your database's native decimal type. – Ryenski Nov 25 '16 at 14:44
  • @mysmallidea, I am using my *database's native decimal type*. – ndnenkov Nov 25 '16 at 15:04
  • 2
    Unless the values are stored in your db extra decimal precision, this method is not likely to have any effect. Most likely it's the value that's being passed in from Ruby that's being rounded incorrectly. You could do `Purchase.where(total: 36.6666.round(2))` – Ryenski Nov 25 '16 at 15:41
1

I think the way you're doing it in your question is the best way. As you know, Ruby is notoriously bad at handling floating point values (try 9.95 * 100). As long as you're storing the values in your database as a decimal value, and not a float, you should be ok.

The only thing I could think of to improve is to extract it into a class method, something like:

# models/purchase.rb
def self.find_approximate(value, delta=0.1)
  where(total: (value - delta)..(value + delta))
end

Purchase.find_approximate(9.95).first
Ryenski
  • 9,582
  • 3
  • 43
  • 47
  • 1
    I wouldn't say Ruby in particular is bad at it. Every language that maps floats to the native implementation will give you 994.999... – ndnenkov Nov 25 '16 at 15:42