codeinspired codeinspired - 10 months ago 46
SQL Question

Rails / Postgres database - can't retrieve correct records from db

I need help troubleshooting a database query to generate correct results. This is a fitness app developed with Rails 4.2.6, Ruby 2.2.4, and a Postgres 1.9.0 database. When a user saves new body measurements, I'm trying to display changes (e.g. inches) since the last measurement. The problem is change values for previously saved records are not calculating correctly in show views.

Here's what I've coded so far:


before_action :get_second_latest_waist_measurement


@waist_second_latest_measurement = @member.fitness_measurements.order(:created_at).offset(1).last.waist || 0



<strong>Change in waist since last measurement:</strong>
<%= (@fitness_measurement.waist - @waist_second_latest_measurement).round(2) %> in

With test data, here's a table which contains results of my database query. Note the values in the "Calc. Change" column.

| Date | Waist(in) | Calc. Change(in) | Correct Change(in) |
| 2016-10-01 | 37.5 | +1.00 | +1.00 |
| 2016-09-01 | 36.5 | 0.00 | +3.00 |
| 2016-08-01 | 33.5 | -3.00 | +0.05 |
| 2016-07-01 | 33.0 | -3.50 | 0.00 |

As you see, except for the last saved record (2016-10-01), the values in the "Calc. Change" column are not correct. There is something wrong with how I've designed the query.

Currently, my query retrieves the record that is 1 below the last saved measurement as the "second latest waist measurement." In the test case, that's the record created on 2016-09-01. This works for the LAST record saved to the database (in this case, 2016-10-01), but produces incorrect results when I request show page views of previously saved records. For instance, the record created on 2016-09-01 should be compared with the one created on 2016--08-01.

Using "offset(1)" in the query appears to be the root of my problem, but I don't know how to get the correct record. Is a solution to iterate through the records? I'm confused about how to do that in this situation?

How should I fix my database query to generate correct change values? If there's a better approach, please let me know. Thank you!

Answer Source

The controller is the wrong place for that code -- for a more simple implementation add a method to the model that holds the saved values.

def previous
  self.class.where(member: self.member).
             where("date < ?",
             order(date: :desc).

This gives you the previous instance for the member, from which you can then read the measurements, allowing logic such as:

@measurement.waist - @measurement.previous.waist