How to calculate total amount in each currency

Question

I'd like to calculate total amount in each currency.

My app image is as followings:

event1    USD 1.23
event2    EUR 2.34
event3    JPY 100
event4    USD 4.56

Total
USD 5.79
EUR 2.34
JPY 100

Although I could calculate amounts with using sum(:amount) regardless of currency, I'd like to know how to calculate and display in each currency.

schedules\show.html.erb

<%= render @schedules %>

schedules\ _schedules.html.erb

<% schedule.rooms.each_with_index do |a, idx| %>
  <% a.events.each do |e| %>
     ...
     <%= l(e.start_at) %>&nbsp;-&nbsp;<%= l(e.end_at) %>
     ...

    <%= e.title %>
    <%= e.ccy %> <%= e.amount %>
  <% end %>

  <%= a.events.sum(:amount) %>   # 108.13 regardless of currency (1.23 + 2.34 + 100 + 4.56)

My models

class Schedule
  has_many :rooms, inverse_of: :schedule, dependent: :destroy
end

class Room    
  belongs_to :schedule, inverse_of: :rooms
  has_many :events, inverse_of: :room, dependent: :destroy
end

class Event
  belongs_to :room, inverse_of: :events
  has_one :schedule, autosave: false, through: :room
end

schedules_controller.rb

def show
  @schedules = Schedule.find(params[:id])
end

schema.rb

create_table "rooms", force: :cascade do |t|
  t.integer  "schedule_id"
  t.integer  "day",                  default: 1
end

create_table "events", force: :cascade do |t|
  t.time     "start_at"
  t.time     "end_at"
  ...
  t.string   "title"
  t.integer  "room_id"
  t.string   "ccy"
  t.decimal  "amount"
end

It would be appreciated if you could give me any suggestion.

UPDATE

When I tried to add <%= room.events.group(:currency).count(:amount) %> as @TheCha͢mp answered, the following error appeared. (the name of variables is original one. I will rename them later.)

ActiveRecord::StatementInvalid in SchedulesController#show

PG::GroupingError: ERROR: column "events.start_at" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...dation_id" = $1 GROUP BY "events"."ccy" ORDER BY "events"."... ^ : SELECT COUNT("events"."amount") AS count_amount, ccy AS ccy FROM "events" WHERE "events"."room_id" = $1 GROUP BY "events"."ccy" ORDER BY "events"."start_at" ASC

I updated _schedules.html.erb and schema.rb

SOLVED

It works with using unscoped. I found it in this post https://makandracards.com/bitcrowd/32883-pg-groupingerror-error-column-must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function-in-rails-while-trying-group.

I added the following code.

<% a.events.unscoped.where(room_id: a.id).group(:ccy).sum(:amount).each do |ccy, amount| %>

Answer 1

First off, your naming is horrible: why name a room a and an event e. And what the heck is ccy? I had to google that one... It makes it hard to read for other developers. I renamed the variables in my solution.

Regarding your issue, you can easily do this on the database level:

Event.group(:currency).count(:amount)
# => {usd: 145, eur: 2289, ...}

So in your view, you can use

<% schedule.rooms.each_with_index do |room, index| %>
  <% room.events.each do |e| %>
    <%= event.title %>
    <%= event.ccy %> <%= event.amount %>
  <% end %>

  <%= room.events.group(:currency).count(:amount) %>
<% end %>

but this would hit the database for every room object, which is inefficient. If you want to prevent this, join the event into your schedule in your controller. This is an entirely different task, but I'm sure you can figure it out.

Answer 2

I recommend writing a method that does the processing, and putting it in a Ruby source file rather than a view template. Then just call the method in the template. Here's a sample script that illustrates a method that should work:

#!/usr/bin/env ruby

require 'awesome_print'


def totals_by_currency(input_lines)
  totals = Hash.new { |h, k| h[k] = 0.0 } # initialize new values to 0.0
  input_lines.each_with_object(totals) do |line, totals|
    _, currency, amount_as_string = line.split
    totals[currency] += amount_as_string.to_f
    totals[currency] = totals[currency].round(2)
  end
end


input =
"event1    USD 1.23
event2    EUR 2.34
event3    JPY 100
event4    USD 4.56
"

ap totals_by_currency(input.split("\n"))

=begin
Outputs:

{
    "USD" => 5.79,
    "EUR" => 2.34,
    "JPY" => 100.0
}

=end

Also, if you would like to ensure 2 decimal places, then you can transform the number to a string using `'%.2f' %:

'%.2f' % 1 # => "1.00"

This could go in the view, since it's just an instruction for how to display the data to the user.