How to query an adjacent previous record to another record you look up in Rails with Active Record?

Question

I am building a Rails app to pull data from the Johns Hopkins Covid-19 Github project and to calculate the daily growth rates for cases and deaths. I started this because I wanted to see the change in the data every day for my city of Nashville. Currently, I have a hard-coded html table, which you can see here, but I'm trying to make it dynamic so that it works for every county in the US.

At this point I'm blocked trying to model the database and calculate the daily growth rates on a before_save callback because I'm not sure how to look up the records I need.

What is a decent way to find a record in rails that is the adjacent previous record to the one you're looking up? I'm not even sure how to say that clearly in English, which is probably the root of my problem with not being able to get it into code. For instance, today vs. yesterday OR March 28th vs. March 27th OR March 31st vs March 30th, etc. I need to find the first record based on date and then find the last record from the day prior.

It should be something similar to this before_save callback:

def calculate_case_growth_rate
  current_cases  = self.cases
  previous_cases = Update.where(date: 1.day.ago).sum(:cases)
  self.case_growth_rate = (current_cases - previous_cases) / current_cases
end

Here's the model where that callback happens.

But, that approach won't work because there are times where the data might be incorrect for a previous date and it will need to get updated. For instance, the Tennessee health department might discover they had erroneous data for March 31st, so the Johns Hopkins data gets updated and thus the record in my database would need to be updated. So with my code above, the first time that happens it would call Update.last and save an incorrect growth rate for March 31st. Thus, I need the callback to work whether it's calculating the growth rate for today vs. yesterday or March 28th vs. March 27th, etc.

It's an open project, so the entire app is here. (Please send a pull request if you'd like to help directly!)

Maueez Ahmed Maueez Ahmed · Accepted Answer · 2020-04-02T15:21:23

From what i have understood is that you need previous day cases. So let's say if today is April 2nd so you need April 1st case then calculate the growth rate. To get the cases from previous day what you can do is to do something like this. Update.where(date: 1.day.ago) this will return cases from the previous day. I have looked up the date attribute from your schema. Hope it helps

UPDATE: So the requirement was to fetch cases from the previous date relative to any current date and then calculate the growth rate of cases which is calculated by

    before_save :calculate_case_growth_rate

    def calculate_case_growth_rate
      current_cases  = self.cases
      previous_cases = Update.where(date: (self.date - 1.day)).sum(:cases)
      self.case_growth_rate = ((current_cases - previous_cases) / current_cases)
    end

How to query an adjacent previous record to another record you look up in Rails with Active Record?

1 Answers