如何在activerecord中订购累计付款?

这应该对你有用:

payments = Payment.arel_table
sum_payments = Arel::Table.new('sum_payments')
payments_total = payments.join(
     payments.project(
       payments[:person_id],
       payments[:amount].sum.as('total')
     )
     .where(payments[:date].between(@range))
     .group( payments[:person_id])
     .as('sum_payments'))
     .on(sum_payments[:person_id].eq(Person.arel_table[:id]))

这将创建断开的sql(不从语法不正确的付款中选择任何内容,也不连接到该查询中甚至不存在的人),但我们实际上只需要连接,例如。

 payments_total.join_sources.first.to_sql
 #=> INNER JOIN (SELECT payments.person_id, 
 #     SUM(payments.amount) AS total 
 #      FROM payments 
 #      WHERE 
 #        payments.date BETWEEN ... AND ...
 #       GROUP BY payments.person_id) sum_payments 
 #      ON sum_payments.id = people.id

所以知道这一点我们可以通过 join_sources 到 ActiveRecord::QueryMethods#joins 让 rails 和 arel 像这样处理其余的事情

current_account
 .people
 .includes(:payments)
 .joins(payments_total.join_sources) 
 .where(:payments => { :date => @range })
 .order("sum_payments.total DESC")

这将导致sql类似于

SELECT 
 -- ...
FROM 
  people
  INNER JOIN payments ON payments.person_id = people.id 
  INNER JOIN ( SELECT payments.person_id,
                 SUM(payments.amount) as total
               FROM payments
               WHERE 
                  payments.date BETWEEN -- ... AND ...
               GROUP BY payments.person_id) sum_payments ON 
     sum_payments.person_id = people.id
WHERE
   payments.date BETWEEN -- ... AND ..
ORDER BY 
   sum_payments.total DESC

这将显示所有在给定日期范围内付款的人(连同这些付款),按付款金额降序排序。

这是未经测试的,因为我没有费心设置整个rails应用程序,但它应该是功能性的。