Ruby on Rails iterate through column efficiently

            created_at            iteration   group_hits_per_iteration
--------------------------------------------------------------------
    2019-11-08 08:14:05.170492      300                    34
    2019-11-08 08:14:05.183277      300                    24
    2019-11-08 08:14:05.196785      300                    63
    2019-11-08 08:14:05.333424      300                    22
    2019-11-08 08:14:05.549140      300                    1
    2019-11-08 08:14:05.576509      300                    15
    2019-11-08 08:44:05.832730      301                    69
    2019-11-08 08:44:05.850111      301                    56
    2019-11-08 08:44:05.866771      301                    18
    2019-11-08 08:44:06.310749      301                    14

Hello My goal is to create a sum total of the values in ‘group_hits_per_iteration’ for each unique value in the ‘iteration column’ which will then be graphed using chartkick.

For example, for iteration 300 I would sum together 34,24,63,22,1,15 for a total of 159, then repeat for each unique entry.

The code I’ve included below does work and generates the required output but it’s slow and gets slower the more data is read into the database.

It creates a hash that is fed into chartkick.

hsh = {}
Group.pluck(:iteration).uniq.each do |x|

date = Group.where("iteration = #{x}").pluck(:created_at).first.localtime
itsum = Group.where("iteration = #{x}").pluck('SUM(group_hits_per_iteration)' )
hsh[date] = itsum
end





<%= line_chart [
  {name: "#{@groupdata1.first.networkid}", data: hsh}

] %>

I’m looking for other ways to approach this, I was thinking of having SQL do the heavy lifting and not do the calculations in rails but not really sure how to approach that.

Thanks for the help.

Answer

If you want to get just the sums for every iteration, following code should work:

# new lines only for readability
group_totals =
  Group
    .select('iteration, min(created_at) AS created_at, sum(group_hits_per_iteration) AS hits')
    .group('iteration')
    .order('iteration') # I suppose you want the results in some order

group_totals.each do |group|
  group.iteration # => 300
  group.hits # => 159
  group.created_at # => 2019-11-08 08:14:05.170492
end

In this case all the hard work is done by the database, you can just read the results in your ruby code.

Note: In your code you are taking first created_at for every iteration, I took the lowest date

Advertisement

Answer