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

Question

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:

controllers/fitness_measurements_controller.rb:

before_action :get_second_latest_waist_measurement

def 

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

end

fitness_measurements/show.html.erb:

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

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!

Ben Trewern · Accepted Answer · 2016-10-10 18:26:12Z

1

I'm not sure of the best way to do this in a "Rails Way" but to just do this in Postgresql you can use window functions:

SELECT *, waist - LAG(waist) OVER (order by date) as change FROM fitness_measurements WHERE member_id = ?

Tried this on PostgreSql 9.3.x and it works without issues. Not tried it in Rails but I guess you'd have to do something like:

sql = "SELECT *, waist - ..."
FitnessMeasurement.find_by_sql(sql)
...

answered Oct 10, 2016 at 18:26

Ben Trewern

1,7401 gold badge11 silver badges15 bronze badges

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

6 Comments

codeinspired Over a year ago

thanks for your reply. I'll look at this and see if I can figure out how to apply this to my Rails project. Need to figure out how to modify my query method code.

codeinspired Over a year ago

the raw SQL causes the following error: syntax error, unexpected ',' sql = SELECT *, waist - LAG(waist) OVER (ord...

Ben Trewern Over a year ago

Did you actually leave out the quotation marks? Was this a ruby error or a postgres error? I should try running the SQL in a "rails dbconsole" to get it working first.

codeinspired Over a year ago

I ran the SQL without the quotation marks. I decided to accept @David Alridge solution... it's cleaner / better than my original approach. However, I appreciate your help.

Ben Trewern Over a year ago

I can't comment on the answer by David Aldridge (not enough reputation) but I'd look at adding a .limit(1) before the .take otherwise it looks like it will return all the previous fitness_measurements from the database instead of just one.

|

David Aldridge · Accepted Answer · 2016-10-10 20:25:28Z

0

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 < ?", self.date).
             order(date: :desc).
             take
end

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

answered Oct 10, 2016 at 20:25

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

1 Comment

codeinspired Over a year ago

Thank you for this solution! This approach is cleaner and much better than I what I had. It works great. Awesome.

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related