问题描述
我想将新列 sum（time_entries.hours）添加到sql select
我正在查询以下条目：
I want to add new column sum(time_entries.hours) to sql selectI'm querying entries like this:
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 select:
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
我尝试插入：select => *，sum（time_entries.hours） 转换为 all 方法的哈希参数，但没有效果。
I tried insert :select=>"*, sum(time_entries.hours)" into hash parameter to all method, but there is no effect.
如何向此选择添加新列？
另外我还需要保留所有当前列，因为它们在过滤器中使用。
有没有不手动指定所有列的方法吗？
How can I add new column to this select?Also I need preserve all current columns, since they are used in filters.Is there any way without specifying all columns by hands?
更新：
Ruby 1.9.3
Rails 3.2.13
UPDATE:Ruby 1.9.3Rails 3.2.13
推荐答案
表名称类似于Redmine。最近我实际上遇到了类似的问题。我发现选定的列来自可见范围。我无法确定为什么会这样，但是如果没有它，选择选项会按预期工作。
The table names look like from Redmine. I actually ran into a similar problem recently. What I found was that the selected colums stem from the visible scope. I could not pin down why exactly but working without it, the 'select' option worked as intended.
使用
Issue.select("#{Issue.table_name}.*, sum(#{TimeEntries.table_name}.hours AS total_hours").where("#{your_conditions}").group(:id).all
应该为您提供所需的数据。可以访问其他列直接作为问题属性：
should give you the data you want. The additional column can be accessed directly as an Issue attribute:
Issue.total_hours
编辑：
如果无法选择所有问题字段（用于创建完整字段问题）和总和，您需要同时获得所需的问题。
If it's not possible to select all Issue fields (for creating the full Issue objects) and the sum at the same time, you need to use a second statement after getting the issues you want.
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
或者，您可以使用Redmines Issue类中包含的便利功能。使用它可以使软件为您完成工作：
Alternatively you can use the included convenience functions in Redmines Issue class. Use this to make the Software do the work for you:
i = Issue.includes(:time_entries).find(1)
i.total_spent_hours
包含内容可确保计算不会影响数据库a第二次，因为所有相关的关联都已加载。
The include makes sure that the calculation doesn't hit the database a second time because all related associations are already loaded.
这篇关于Rails-活动记录：添加额外的选择列以查找（：all）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！