Ruben Barbosa Ruben Barbosa - 6 months ago 15
SQL Question

Mysql2::Error: Unknown column 'sum_hours' in 'field list'

Its been nearly 2 months since i started to learn rails for doing some modifications on a plugin of Redmine in an enterprise (as a student learning the way of "life"), but this problem is something i cant find the solution anywhere.

Let me explain:

I needed to group a huge ammount of time entries by user_id and project_id(no problems with that) but if i do this i need to Sum the hours in those groups.

The estructure is something like this:


| ID | project_id | user_id | issue_id | hours |


And this information is built up with this method :

def time_entries_for_user(user, options={})
extra_conditions = options.delete(:conditions)
return TimeEntry.select('*,sum(hours) as sum_hours').
includes(self.includes).
where(self.conditions([user], extra_conditions)).
group('issue_id, time_entries.project_id').
order('issues.id ASC')
end


I added the select because i needed all the column plus the sum one and the group by alone works well , but i cant make the Sum columns appear.
This is the json code from the result of this method:

{"test1 / SubProyectoTest1":{"logs":[{"id":24,"project_id":4,"user_id":1,"issue_id":10,"hours":6.0,"comments":"","activity_id":8,"spent_on":"2016-05-03","tyear":2016,"tmonth":5,"tweek":18,"created_on":"2016-05-03T11:07:09.000Z","updated_on":"2016-05-03T11:07:09.000Z","sum_hours":9.0}],"users":[{"id":1,.....


The funny thing is that in other method which groups by project , the Sum column appear.
This one works well :

def time_entries_for_all_users(project)
return project.time_entries.select('*,sum(hours) as sum_hours').
includes(self.includes).
where(self.conditions(self.users)).
group('issue_id,user_id')
order('issues.id ASC')
end


{"test1 / SubProyectoTest1":{"logs":[{"id":24,"project_id":4,"user_id":1,"issue_id":10,"hours":6.0,"comments":"","activity_id":8,"spent_on":"2016-05-03","tyear":2016,"tmonth":5,"tweek":18,"created_on":"2016-05-03T11:07:09.000Z","updated_on":"2016-05-03T11:07:09.000Z","sum_hours":9.0}],"users":[{"id":1.......


In the logs, this error shows :


ActionView::Template::Error (undefined method `sum_hours' for #)


Referring to this code on the view that renders the time entries:

<% when l(:field_hours) %>
<strong><%= number_with_precision(time_entry.sum_hours, :precision => @precision) %></strong>


Well, you cant call something that doesn't exists...

2ยบ time asking for help in StackOverflow so if you need more info because i forgot to write it , do it :) .

Regards and thanks.

EDITED:

Thanks to BoraMa y Emiliano, i get it to work adding the relation manually.
The code of the first sample changed to :

def time_entries_for_user(user, options={})
extra_conditions = options.delete(:conditions)

return TimeEntry.select('*,sum(hours) as sum_hours').
includes(self.includes).
where(self.conditions([user], extra_conditions)).
where("time_entries.user_id",user)
group('issue_id, time_entries.project_id').
order('issues.id ASC')
end
end

Answer

In my tests, the manually selected column (sum_hours in your case), is normally accessible as a "virtual" attribute when you execute the query on a model, as per your first example - the time_entries_for_user method.

However, it does not seem to work if you try to add a custom select to an association of a record. Then I get the exact same error as you (a NoMethodError). For this to start working, I'd advise to rewrite your second query (the time_entries_for_all_users) to a form without using associations, i.e. to something like this:

def time_entries_for_all_users(project)
  TimeEntry.select('*,sum(hours) as sum_hours').     # <- no association used here
      includes(self.includes).
      where(self.conditions(self.users)).
      where("time_entries.project_id", project.id).  # <- the assoc. is added here instead
      group('issue_id,user_id')
      order('issues.id ASC')
end

Nothe that instead of calling the time_entries association on the current project record, we are selecting all time entries having this project ID. This should return the same records as your original query but the custom columns should also work correctly. You'll probably need to update the query further so that your other conditions and includes work ok too.

As a second option, you can always define the association together with the manual select:

has_many :time_entries_with_hours_sum,  
         -> { select("time_entries.*, sum(hours) as sum_hours") }, class_name: "TimeEntry"

Then your custom column should be normally accessible using this new association:

project.time_entries_with_hours_sum.first.sum_hours
# => 9.0
Comments