Rails 4 Group by function not working

Question

I have the following table

Employee

Fields are

id
annual_leave_total
group_id (FK)

And I writing a simple sum group by query by stating getting a sum of annual_leave_totals grouped by group_id ie

Employee.select("department_id, sum(annual_leave_total)").group(:group_id)

But what I get instead in the console is the following

irb(main):010:0> Employee.select("group_id, sum(annual_leave_total)").group(:group_id)
 Employee Load (1.0ms)  SELECT group_id, sum(annual_leave_total) FROM "employees" GROUP     BY group_id
=> #<ActiveRecord::Relation [#<Employee id: nil, group_id: 1>, #<Employee id: nil, group_id: 2>, #<Employee id: nil, group_id: 3>]>

Why is it returning this hash type of result? It's different to what expected in an actual sql query...

Any ideas?

This is what I'm supposed to see group_id | sum ----------+------ 1 | 1275 2 | 600 3 | 450 — awongCM, Apr 11 '14 at 13:56
Try using `employees = Employee.select("department_id, sum(annual_leave_total) as annual_leave_total").group(:group_id)` and access to this sum by doing: `employees.first.annual_leave_total` — MrYoshiji, Apr 11 '14 at 13:56
I redid the code. Employee.select("group_id as Department, sum(annual_leave_total) as annual_leave_total").group(:group_id) And now the results come back with my actual totals column! #, #, #]> — awongCM, Apr 11 '14 at 14:00
But I'd find this bizzare that I have to explicit about my grouping functions in the select clause. I mean in the actual sql world, you can opt out the column names when grouping aggregate functions. But in ruby/rails, they seem very restricted on this type of flexibility? Why?? — awongCM, Apr 11 '14 at 14:02

score 1 · Accepted Answer · edited May 23 '17 at 12:27

Much like I wrote in another question (when i was using find_by_sql alias, i got confused), AREL simply tries to map your query to the Employee model. Since you start your query from the Employee class as a base, you will get an object of this type back.

In your query you only selected the group_id, which is an attribute of the class, and a sum of one of the attributes (but not the attribute itself!). Now keep in mind that what you printed out is simply a to_s representation of the Employee object which only knows about its own attributes you listed in your post. It doesn't know about an attribute called sum(annual_leave_total), so it isn't printed.

However, the value can still be accessed through the attributes hash though by calling employee.first.attributes. The hash should include all of the class attributes (most as nil, as you didn't select them) as well as the additional ones. The sum value just doesn't have a getter since it's not an 'official' attribute of the class.

Rails 4 Group by function not working

1 Answers1