ruby-on-rails - 按关联属性的总和排序

Question

我有一个带有费用关联的部门模型:

class Department < ActiveRecord::Base
  has_many :expenses
end

class Expense < ActiveRecord::Base
  belongs_to :department
end

费用具有金额属性:

e = Expense.new
e.amount = 119.50

我现在想要 2 个查询:

列出所有部门，按费用总和排序。

与#1 相同，但按月份分组，即一月、二月、三月、...

Answer 1

对于#1，以下代码将为您提供按费用总和排序的部门 ID:

Expense.select('department_id, sum(amount) as total').group('department_id').order('total desc')

以下是有关如何使用返回对象的示例代码:

Expense.select('department_id, sum(amount) as total').group('department_id').order('total desc').each { |dep| print "Department ID: #{dep.department_id} | Total expense: #{dep.total}\n" }

这将打印如下内容:

部门编号:2 |总费用:119.50
部门编号:1 |总费用:54.34
部门编号:10 |总费用:23.43

对于#2，您可以类似地将月份分组与总和一起添加:

Expense.select('department_id, extract(month from created_at) as month, sum(amount) as total').group('department_id, month').order('month asc, total desc')

同样，一个示例代码来演示如何使用它:

Expense.select('department_id, extract(month from created_at) as month, sum(amount) as total').group('department_id, month').order('month asc, total desc').each { |dep| print "Department ID: #{dep.department_id} | Month: #{dep.month} | Total expense: #{dep.total}\n" }

这将打印如下内容:

部门编号:2 |月:1 |总费用:119.50
部门编号:1 |月:1 |总费用:54.34
部门编号:10 |月:1 |总费用:23.43
部门编号:1 |月:2 |总费用:123.45
部门编号:2 |月:2 |总费用:76.54
部门编号:10 |月:2 |总费用:23.43

... 等等。

当然，一旦获得了部门 ID，就可以使用 Department.find() 来获取其余信息。我相信 ActiveRecord 不支持在不使用原始 SQL 的情况下直接同时获取所有部门字段。

编辑----

如果要包括部门字段，您可以:

1 - 在单独的查询中加载它们，例如:

Expense.select('department_id, sum(amount) as total').group('department_id').order('total desc').each do |department_expense|
    # In department_expense you have :department_id and :total
    department = Department.find(department_expense.department_id)
    # In department now you have the rest of fields
    # Do whatever you have to do with this row of department + expense
    # Example
    print "Department #{department.name} from #{department.company}: $#{department_expense.total}"
end

优点:使用 ActiveRecord SQL 抽象很好而且干净。
缺点:您总共进行了 N+1 次查询，其中 N 是部门数，而不是单个查询。

2 - 使用原始 SQL 加载它们:

Department.select('*, (select sum(amount) from expenses where department_id = departments.id) as total').order('total desc').each do |department|
    # Now in department you have all department fields + :total which has the sum of expenses
    # Do whatever you have to do with this row of department + expense
    # Example
    print "Department #{department.name} from #{department.company}: $#{department.total}"
end

优点:您正在执行单个查询。
缺点:您正在失去 ActiveRecord 从 SQL 提供给您的抽象。

两者都会打印:

来自微软的部门研发:119.50 美元
雅虎的部门财务:54.34 美元
来自 Google 的部门设施:23.43 美元