mysql - ActiveRecord complex calculation - how to solve it the rails way? -

currently i'm trying figure out how calculation proper , best way using rails 4.1.

(simplified example)

movement (activerecord model)

id: integer paid_at: datetime value: decimal debit_account_id: references credit_account_id: references category_id: references ... 

the goal

i want 4 column list result, having:

1. the paid_at date (grouped by)
2. the sum of values paid_at date, if condition one true
3. the sum of values paid_at date, if condition two true
4. the difference of both values (column 2 minus column 3)

conditions may different, easy case be:

• condition 1: debit_account_id in (3, 4)
• condition 2: credit_account_id in (3, 4)
• just one: field of joined account must have field match value
• ...

example result

 date       | earnings | spendings |  total |  -----------+----------+-----------+--------+  2015-01-01 |   120.00 |     50.00 |  70.00 |  2015-01-05 |     0.00 |     10.00 | -10.00 |  2015-01-06 |   100.00 |      0.00 | 100.00 |  ... 

one possible (ugly) sql way

select   date(`paid_at`) `date`,   sum(if(credit_account_id in(:credit_accounts), value, 0)) `earnings`,   sum(if(debit_account_id in(:debit_accounts), value, 0)) `spendings`,   sum(if(credit_account_id in(:credit_accounts), value, 0)) - sum(if(debit_account_id in(:debit_accounts), value, 0)) `total`    movements   credit_account_id in (:credit_accounts) or   debit_account_id in (:debit_accounts) group `date` order `date`; 

the question

how can expected result in better way? , how using rails?

~~ thank in advance help! :d ~~

if split 2 queries, can begin break down manageable scopes in models.

first, can set conditions 1 , 2 in account models:

# creditaccount model scope :for_report, -> { where(id: [3, 4]) }  # debitaccount model scope :for_report, -> { where(id: [3, 4]) } 

these match example conditions of in (3, 4) can updated whatever account conditions necessary.

then, in movement model can set scopes sum credits , debits date:

# movement model scope :earnings, -> { joins(:credit_account).merge(creditaccount.for_report) } scope :spendings, -> { joins(:debit_account).merge(debitaccount.for_report) } scope :sum_by_date, -> { group("date(paid_at)").order("date(paid_at)").sum(:value) } 

then example of how report want this:

# movement model def self.report   earnings = movement.earnings.sum_by_date    # query earnings date   spendings = movement.spendings.sum_by_date  # query spendings date   dates = (earnings.keys + spendings.keys).uniq.sort   printf "%-12s %12s %12s %12s\n", "date", "earnings", "spendings", "total"   dates.each |date|     credit = earnings[date] || 0     debit = spendings[date] || 0     printf "%-12s %12s %12s %12s\n", date, credit, debit, (credit - debit)   end end 

then if call movement.report rails console give example output.