ruby-on-rails – 按协会财产的总和排序

发布时间：2020-12-17 02:39:39 所属栏目：百科来源：网络整理

导读：我有一个费用协会的部门模型： class Department ActiveRecord::Base has_many :expensesendclass Expense ActiveRecord::Base belongs_to :departmentend 费用有金额属性： e = Expense.newe.amount = 119.50 我现在想要2个查询：列出所有部门,按费用的总

我有一个费用协会的部门模型：

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相同,但按月分组,即jan,feb,march,……

解决方法

对于#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
部门ID：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,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
部门ID：10 |月：1 |总费用：23.43
部门编号：1 |月：2 |总费用：123.45
部门编号：2 |月：2 |总费用：76.54
部门ID：10 |月：2 |总费用：23.43

… 等等.

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

编辑—-

如果要包含部门字段,您可以：

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提供给您的抽象.

两者都会打印：

微软部门R& D：119.50美元雅虎的部门财务：54.34美元谷歌的部门设施：23.43美元

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!