how to update a computed result of several columns in one record into postgresql database with rails

Question

I have a table called "Scores" which has 4 columns, "first", "second", "third", and "average" for keeping record of user's score.

When a user create the record initially, he can leave "average" column blank. Then he can edit all 3 scores later.

After editing, the user can see the computed average (or sum, or any calculation result.) in his show page, since I have

def show @ave = (@score.first + @score.second + @score.third)/3 end

However, @ave is not in the database, how can I update @ave into the column of "average" of my database?

1. Ideally, it would be the best if the computing takes place before updating into database, so all 4 values can be updated into database together. It might have something to do with Active Record Callbacks, but I don't know how to do that.

2. Second approach, I think i need a "trigger" in database so that it can compute and update "average" column as soon as other 3 columns got updated. If this is how you do it, please let me know and the advantage of comparing with solution number 1.

3. Last approach, since the user already know the average in his show page, I don't have to update the computed average into "average" column immediately. I think i can leave this to a delayed_job or background job. If this is how you do it, please let know me how.

Thank you in advance!(ruby 2.3, rails 5.0.1, postgresql 9.5

Answer 1

Unless you really do need the average stored in the database for some reason, I would add an attribute to the Score model:

def average
  (first + second + third)/3.0
end

If one or more might not be present, I would:

def average
  actual_scores = [first, second, third].compact
  return nil if actual_scores.empty?
  actual_scores.sum / actual_scores.size
end

If you do need the average saved, then I would add a before_validate callback:

before_validation do
  self.average = (first + second + third)/3.0
end