mysql - ActiveRecord 复杂计算——如何用rails方式解决？

Question

目前我正在尝试弄清楚如何使用 Rails 4.1 以正确和最佳的方式进行计算。

(简化示例)

移动(ActiveRecord 模型)

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

目标

我想要一个四列列表作为结果，具有:

1. paid_at 日期(分组依据)
2. paid_at 日期所有值的总和，如果条件 ONE 为真
3. paid_at 日期所有值的总和，如果条件二为真
4. 两个值的差(第 2 列减去第 3 列)

条件可能不同，一个简单的例子是:

• 条件 1: debit_account_id IN (3, 4)
• 条件 2: credit_account_id IN (3, 4)
• 另一个:加入帐户的任何字段都必须有一个字段匹配一个值
• ...

示例结果

 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 |
 ...

一种可能的(丑陋的)SQL 方式

SELECT
  DATE(`paid_at`) AS `date`,
  SUM(IF(credit_account_id IN(:credit_accounts), value, 0)) AS `earnings`,
  SUM(IF(debit_account_id IN(:debit_accounts), value, 0)) AS `spendings`,
  SUM(IF(credit_account_id IN(:credit_accounts), value, 0)) - SUM(IF(debit_account_id IN(:debit_accounts), value, 0)) AS `total`
FROM 
  movements
WHERE
  credit_account_id IN (:credit_accounts)
OR
  debit_account_id IN (:debit_accounts)
GROUP BY `date`
ORDER BY `date`;

问题

如何以更好的方式获得预期的结果？以及如何使用 Rails 做到这一点？

~~提前感谢您的帮助! :D ~~

Answer 1

如果我们将其拆分为两个查询，我们就可以开始将其分解为模型中可管理的范围。

首先，我们可以在账户模型中设置条件1和2:

# CreditAccount Model
scope :for_report, -> { where(id: [3, 4]) }

# DebitAccount Model
scope :for_report, -> { where(id: [3, 4]) }

这些目前与您的示例条件 IN (3, 4) 相匹配，但它们可以根据需要的任何帐户条件进行更新。

然后，在 Movement 模型中，我们可以设置将按日期对贷项和借项求和的范围:

# 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) }

那么如何获取您想要的报告的示例可能是这样的:

# Movement Model
def self.report
  earnings = Movement.earnings.sum_by_date    # Query for earnings by date
  spendings = Movement.spendings.sum_by_date  # Query for spendings by date
  dates = (earnings.keys + spendings.keys).uniq.sort
  printf "%-12s %12s %12s %12s\n", "Date", "Earnings", "Spendings", "Total"
  dates.each do |date|
    credit = earnings[date] || 0
    debit = spendings[date] || 0
    printf "%-12s %12s %12s %12s\n", date, credit, debit, (credit - debit)
  end
end

然后，如果您从 Rails 控制台调用 Movement.report，它会给出一些示例输出。