CODEWITHSUNDEEP
ruby-on-railsactiverecord
rudolph9
rudolph9

Reputation: 8119

find_by not behaving as expected

Working from the rails console, why is the following find_by returning nil?

2.0.0 (main):0 > Location.last
  Location Load (0.8ms)  SELECT "locations".* FROM "locations" ORDER BY "locations"."id" DESC LIMIT 1
=> #<Location id: 92, district: nil, address: "8026 S KEDZIE AVE", city: "CHICAGO", state: "IL", zip: 60652, website: nil, location: nil, latitude: 41.7474748619454, longitude: -87.7023800625442, location_type: "fire_station">
2.0.0 (main):0 > lat = Location.last.latitude
  Location Load (0.8ms)  SELECT "locations".* FROM "locations" ORDER BY "locations"."id" DESC LIMIT 1
=> 41.7474748619454
2.0.0 (main):0 > Location.find_by( latitude: lat)
  Location Load (0.8ms)  SELECT "locations".* FROM "locations" WHERE "locations"."latitude" = 41.7474748619454 LIMIT 1
=> nil

I originally thought it something to do with Sqlite so I switched over to Postgres but still yield the same result. Someone else working on the some project ran the same find_by yielded the proper result. What is going on with my system that this is happening?

Upvotes: 1

Views: 65

Answers (2)

Nikolay
Nikolay

Reputation: 1392

As a quick workaround, you could use SQL ROUND method

Location.find_by("ROUND(latitude, 3) = ?", "%.3f" % lat)

but it'll effectively kill your index on the locations table (assuming you have one) and not really suitable for large amounts of data.

Upvotes: 0

Peter Alfvin
Peter Alfvin

Reputation: 29379

Assuming this is a problem with Float equality check, here are some suggestions from http://www.mail-archive.com/[email protected]/msg02703.html:

Given: x = 11.967 y = 11.966999999999 <-- loss of precision due to hex<->decimal conversion

  1. Compare based on a range (as suggested in a prior post) 11.9670 <= x < 11.968.

  2. Compare string representations of the floating point values: x == y => false ("%.3f" % x) == ("%.3f" % y) => true

  3. Store values using a fixed point data type (such as DECIMAL in MySQL): lng DECIMAL(5,3) x -> lng => 11.967 y -> lng => 11.967 <-- rounded based on the rules defined in the database Making it safe to compare equality on the two values

  4. Store fixed decimal values as integers in the database and convert them when displaying them. I only mention this technique because it is an option for storing currency values. Rather than store dollars, store cents instead and convert to dollars with displaying the values on the view. $1.60 -> 160 (in the database)

Upvotes: 2

Related Questions