CODEWITHSUNDEEP
ruby-on-rails-3activerecord
pejmanjohn
pejmanjohn

Reputation: 1057

Running total in Rails and Active Record

I have a table with workouts with the following key columns:

workout_id      distance
1                       2.3
2                       3.1

Let's say I want to write a Rails active record query so that I get these columns plus a running total of the distance, like so:

workout_id      distance      running_total
1                       2.3                 2.3
2                       3.1                 5.4

How could I do such a thing?

Upvotes: 1

Views: 1280

Answers (2)

rails_has_elegance
rails_has_elegance

Reputation: 1692

To do it closer to the object, although this not being the most effective way, you could do for example:

<% Workout.all.map{|x| x.attributes}.each_with_index{|e, i| e["running_total"] = Workout.all.take(i + 1).map{|x| x.distance}.inject{|sum,x| sum + x}}.each do |workout| %>
    <%= workout["id"] %>
    <%= workout["distance"] %>
    <%= workout["running_total"] %>
<% end %>

But notice how many sql queries this is performing. Probably not your best solution. My code itself also probably could get optimized, but in general your approach might not be the best idea as others already pointed out.

Upvotes: 0

agmcleod
agmcleod

Reputation: 13621

I agree with HungryCoder that you should perhaps do it in the model rather than in a database query.

class Workout
  # ...
  attr_accessor :running_total
  class << self
    def distance_with_running_total
      total = 0.0
      Workout.order(:id).collect do |w|
        total += w.distance
        w.running_total = total
      end
    end
  end
  # ...
end

Then in the controller, simply do:

@workouts = Workout.distance_with_running_total

Upvotes: 3

Related Questions