Ruby-on-rails 使用 Activerecord 对多列求和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21916047/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sum on multiple columns with Activerecord
提问by Joy
I am new to Activerecord. I want to do sum on multiple columns of a model Student. My model student is like following:
我是 Activerecord 的新手。我想对模型学生的多列进行求和。我的模范学生如下：
class Student < ActiveRecord::Base
attr_accessible :class, :roll_num, :total_mark, :marks_obtained, :section
end
I want something like that:
我想要这样的东西：
total_marks, total_marks_obtained = Student.where(:id=>student_id).sum(:total_mark, :marks_obtained)
But it is giving following error.
但它给出了以下错误。
NoMethodError: undefined method `except' for :marks_obtained:Symbol
So I am asking whether I have to query the model two times for the above, i.e. one to find total marks and another to find marks obtained.
所以我问我是否必须为上述模型查询两次，即一次查找总分，另一次查找获得的分数。
采纳答案by penner
You can use raw SQL if you need to. Something like this to return an object where you'll have to extract the values... I know you specify active record!
如果需要，您可以使用原始 SQL。像这样返回一个对象，您必须在其中提取值...我知道您指定了活动记录！
Student.select("SUM(students.total_mark) AS total_mark, SUM(students.marks_obtained) AS marks obtained").where(:id=>student_id)
For rails 4.2 (earlier unchecked)
对于 rails 4.2（之前未选中）
Student.select("SUM(students.total_mark) AS total_mark, SUM(students.marks_obtained) AS marks obtained").where(:id=>student_id)[0]
NB the brackets following the statement. Without it the statement returns an Class::ActiveRecord_Relation, not the AR instance. What's significant about this is that you CANNOT use firston the relation.
注意语句后面的括号。没有它，语句返回一个 Class::ActiveRecord_Relation，而不是 AR 实例。重要的是你不能first在关系上使用。
....where(:id=>student_id).first #=> PG::GroupingError: ERROR: column "students.id" must appear in the GROUP BY clause or be used in an aggregate function
回答by AlexGuti
You can use pluckto directly obtain the sum:
您可以使用pluck直接获取总和：
Student.where(id: student_id).pluck('SUM(total_mark)', 'SUM(marks_obtained)')
# SELECT SUM(total_mark), SUM(marks_obtained) FROM students WHERE id = ?
You can add the desired columns or calculated fields to pluckmethod, and it will return an array with the values.
您可以将所需的列或计算字段添加到pluck方法，它将返回一个包含值的数组。
回答by YasirAzgar
If you just want sum of columns total_marks and marks_obtained, try this
如果你只想要列 total_marks 和marks_obtained 的总和，试试这个
Student.where(:id=>student_id).sum('total_mark + marks_obtained')
回答by Marc Greenstock
Another method is to ActiveRecord::Calculations.pluckthen Enumerable#sumon the outer array and again on the inner array pair:
另一种方法是ActiveRecord::Calculations.pluck，然后Enumerable#sum外阵列上，并再次于内阵列对：
Student
.where(id: student_id)
.pluck(:total_mark, :marks_obtained)
.map(&:sum)
.sum
The resulting SQL query is simple:
生成的 SQL 查询很简单：
SELECT "students"."total_mark",
"students"."marks_obtained"
FROM "students"
WHERE "students"."id" =
The initial result of pluckwill be an array of array pairs, e.g.:
的初始结果pluck将是一个数组对数组，例如：
[[10, 5], [9, 2]]
.map(&:sum)will run sumon each pair, totalling the pair and flattening the array:
.map(&:sum)将sum在每一对上运行，合计该对并展平数组：
[15, 11]
Finally .sumon the flattened array will result in a single value.
最后.sum在扁平数组上将产生一个单一的值。
Edit:
编辑：
Note that while there is only a single query, your database will return a result row for each record matched in the where. This method uses ruby to do the totalling, so if there are many records (i.e. thousands), this maybe slower than having SQL do the calculations itself like noted in the accepted answer.
请注意，虽然只有一个查询，但您的数据库将为where. 此方法使用 ruby 进行总计，因此如果有很多记录（即数千条记录），这可能比让 SQL 自己进行计算（如已接受的答案中所述）要慢。
回答by willyab
Similar to the accepted answer, however, I'd suggest using arel as follows to avoid string literals (apart from renaming columns, if needed).
然而，与接受的答案类似，我建议按如下方式使用 arel 来避免字符串文字（除了重命名列，如果需要）。
Student
.where(id: student_id).
.where(Student.arel_table[:total_mark].sum, Student.arel_table[:marks_obtained].sum)
which will give you an ActiveRecord::Relationresult over which you can iterate, or, as you'll only get one row, you can use .first(at least for mysql).
这会给你一个ActiveRecord::Relation你可以迭代的结果，或者，因为你只会得到一行，你可以使用.first（至少对于mysql）。
