gpt4 book ai didi

ruby-on-rails - Rails - 事件记录 : add extra select column to find(:all)

转载 作者:行者123 更新时间:2023-12-04 02:02:21 25 4
gpt4 key购买 nike

我想添加新列 sum(time_entries.hours)到 sql 选择
我正在查询这样的条目:

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"
)

它生成这个选择:
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 的散列参数方法,但没有效果。

如何向此选择添加新列?
我还需要保留所有当前列,因为它们用于过滤器。
有没有办法不手动指定所有列?

更新:
ruby 1.9.3
rails 3.2.13

最佳答案

表名看起来像来自 Redmine。我最近实际上遇到了类似的问题。我发现选定的列来自可见范围。我无法确切地确定原因,但没有它就可以工作,“选择”选项按预期工作。

使用

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

应该给你你想要的数据。附加列可以作为问题属性直接访问:
Issue.total_hours

编辑:

如果无法同时选择所有问题字段(用于创建完整问题对象)和总和,则需要在获得所需问题后使用第二个语句。
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 类中包含的便利功能。使用它使软件为您完成工作:
i = Issue.includes(:time_entries).find(1)
i.total_spent_hours

包含确保计算不会再次访问数据库,因为所有相关关联都已加载。

关于ruby-on-rails - Rails - 事件记录 : add extra select column to find(:all),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/18750196/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com