Amount across multiple columns using Activerecord

Question

I am new to Activerecord. I want to make a sum on several columns of the Student model. My model student is as follows:

class Student < ActiveRecord::Base attr_accessible :class, :roll_num, :total_mark, :marks_obtained, :section end 

I want something like this:

  total_marks, total_marks_obtained = Student.where(:id=>student_id).sum(:total_mark, :marks_obtained) 

But this leads to the following error.

 NoMethodError: undefined method `except' for :marks_obtained:Symbol 

So, I ask if I need to query the model twice for the above, i.e. find general grades, and the other - find the resulting marks.

Answer 1

You can use raw SQL if you need to. Something like this to return an object where you would need to extract the values ​​... I know that you are indicating an active record!

 Student.select("SUM(students.total_mark) AS total_mark, SUM(students.marks_obtained) AS marks obtained").where(:id=>student_id) 

For rails 4.2 (not previously installed)

 Student.select("SUM(students.total_mark) AS total_mark, SUM(students.marks_obtained) AS marks obtained").where(:id=>student_id)[0] 

Note the brackets after the statement. Without it, the operator returns the :: ActiveRecord_Relation class, and not an AR instance. What is important is that you CANNOT use first in a relationship.

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

Answer 2

If you just need the sum of the columns total_marks and marks_obtained try this

 Student.where(:id=>student_id).sum('total_mark + marks_obtained') 

Answer 3

Another method is ActiveRecord::Calculations.pluck , then Enumerable#sum on the external array and again on the internal pair of the array:

 Student .where(id: student_id) .pluck(:total_mark, :marks_obtained) .map(&:sum) .sum 

The resulting SQL query is simple:

 SELECT "students"."total_mark", "students"."marks_obtained" FROM "students" WHERE "students"."id" = $1 

The original pluck result is an array of pairs of arrays, for example:

 [[10, 5], [9, 2]] 

.map(&:sum) will run sum for each pair, adding up the pair and smoothing the array:

 [15, 11] 

Finally .sum on a flattened array will result in a single value.

---

Edit:

Note that although there is only one query, your database will return a row of results for each record matched in where . This method uses ruby ​​to perform the summation, so if there are many records (i.e., Thousands), it may be slower than SQL to do the calculations themselves, as indicated in the accepted answer.

Answer 4

You can use pluck to directly receive the amount:

 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 necessary columns or calculated fields to the pluck method, and it will return an array with values.