Rails active record: add an extra selection column to find (: all)

Question

I want to add a new sum(time_entries.hours) column sum(time_entries.hours) to sql select. I am requesting the following entries:

 issues = Issue.visible.where(options[:conditions]).all( :include => ([:status, :project, :time_entries] + (options[:include] || [])).uniq, :conditions => statement, :order => order_option, :joins => query_joins(order_option.join(',')), :limit => options[:limit], :offset => options[:offset], :group => "#{Issue.table_name}.id" ) 

It generates this selection:

 SELECT "issues"."id" AS t0_r0, ... "time_entries"."hours" AS t3_r4, ... "versions"."sharing" AS t8_r9 FROM "issues" LEFT OUTER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT OUTER JOIN "issue_statuses" ON "issue_statuses"."id" = "issues"."status_id" LEFT OUTER JOIN "time_entries" ON "time_entries"."issue_id" = "issues"."id" LEFT OUTER JOIN "users" ON "users"."id" = "issues"."assigned_to_id" LEFT OUTER JOIN "trackers" ON "trackers"."id" = "issues"."tracker_id" LEFT OUTER JOIN "enumerations" ON "enumerations"."id" = "issues"."priority_id" AND "enumerations"."type" IN ('IssuePriority') LEFT OUTER JOIN "issue_categories" ON "issue_categories"."id" = "issues"."category_id" LEFT OUTER JOIN "versions" ON "versions"."id" = "issues"."fixed_version_id" WHERE "issues"."id" IN (1) AND (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name = 'issue_tracking')) AND ((issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed = 'f'))) GROUP BY issues.id ORDER BY issues.id DESC 

I tried to insert :select=>"*, sum(time_entries.hours)" into the hash parameter all , but there is no effect.

How to add a new column for this selection? Also I need to keep all current columns as they are used in filters. Is there a way without specifying all columns with my hands?

UPDATE: Ruby 1.9.3 Rails 3.2.13

Answer 1

Table names look like Redmine. I recently encountered a similar problem. I found that the selected colonies come from visible volume. I could not determine exactly why, but without it, the option "choose" worked as intended.

Using

 Issue.select("#{Issue.table_name}.*, sum(#{TimeEntries.table_name}.hours AS total_hours").where("#{your_conditions}").group(:id).all 

should provide you the necessary data. An additional column can be obtained directly as the "Problem" attribute:

 Issue.total_hours 

Edit:

If you cannot select all the Problem fields (to create complete Problem objects) and the sum at the same time, you need to use the second operator after getting the problems you need.

 issues = Issue.visible.where("#{issue_conditions}").all issue_ids = issues.map(&:id) # if you do not need the issue objects, use .pluck(:id) instead of .all spent_hours = TimeEntries.select('issue_id, sum(hours)') .where(:issue_id => issue_ids) .where("#{other_time_conditions}") .group(:issue_id).all 

---

Alternatively, you can use the included convenience features in the Redmines Issue class. Use this to make the software for you:

 i = Issue.includes(:time_entries).find(1) i.total_spent_hours 

The inclusion ensures that the calculation does not get into the database a second time because all associated associations are already loaded.

Answer 2

A bit old, but something like this should work. (On the rails 5 ish)

 issues = Issue. ... issues.select( issues.arel.projections, TimeEntry.arel_table[:hours].sum )