Tuesday, 1 October 2013

Report using Rails ActiveRecord group by

Report using Rails ActiveRecord group by

I am trying to generate a report to screen of accounting transaction
history. In most situations it is one display row per record in the
AccountingTransaction table. But occasionally there are transactions that
I wish to display to the end user as one transaction which are really,
behind the scenes, two accounting transactions. This is caused by deferral
of revenues and fund splitting since this app is a fund accounting app.
If I display all rows one by one, those double entries look odd to the
user since the fund splitting and deferral is "behind the scenes". So I
want to roll up all the related transactions into one display row on
screen.
I have my query now using group by to group the related transactions
@history = AccountingTransaction.where("customer_id in (?) AND no_download
<> 1", customers_in_account).group(:transaction_type_id,
:reference_id).order(:created_at)
as I loop through I get the transactions grouped as I want but I am
struggling with how to display the total sum of the 'credit' field for all
records in the group. (It is only showing the credit for the first record
of the group) If I add a .sum(:credit) to my query, of course, it returns
the sums just as I want but not all the other data.
Is there a way for me to group these records like in my @history query and
also get the sum of the credit field for each respective group?

No comments:

Post a Comment