需要帮助把一个SQL查询到一个ActiveRecord查询
我有以下SQL代码:需要帮助把一个SQL查询到一个ActiveRecord查询
SELECT u.full_name, pu.task_name, sum(hours)
FROM efforts
INNER JOIN project_tasks pu ON efforts.project_task_id = pu.id
INNER JOIN users u ON efforts.user_id = u.id
GROUP BY user_id, task_name
它输出的所有用户,他们的任务,他们的时间。我现在试图做的是将其转换为Rails的ActiveRecord查询。
我想让它看起来类似于我在下面做的事情,但似乎无法让我的逻辑正确。
Project.all.each do |project|
projdata = { 'name' => project.project_name.to_s,
'values' => [] }
['Pre-Sales','Project','Fault Fixing','Support'].each do |taskname|
record = Effort.sum(:hours,
:joins => :project_task,
:conditions => { "project_tasks.project_id" => project.id,
"project_tasks.task_name" => taskname })
projdata[ 'values' ].push(record)
end
@data.push(projdata)
end
end
end
添加的图像链接
链路示出的曲线图。我需要做的是将我的SQL语句转换为一个activeRecord查询,它将像我提供的那样将其显示为我的其他图形。
SELECT u.full_name, pu.task_name, hours
FROM efforts
INNER JOIN project_tasks pu ON efforts.project_task_id = pu.id
INNER JOIN users u ON efforts.user_id = u.id
GROUP BY user_id, task_name
Effort.find(:all, :select => "users.full_name, project_tasks.task_name, hours", :joins => [:user, :project_task], :group => "users.user_id, project_tasks.task_name")
但是,我有一个疑问,你怎么能得到“小时”字段,而不添加它在分组部分。所以,最好还是在分组部分中添加小时。
但是,你应该在以下文件做一些修改
供应商/插件/ expandjoinquery/init.rb”
class ActiveRecord::Base
class << self
private
def add_joins!(sql, options, scope = :auto)
scope = scope(:find) if :auto == scope
join = (scope && scope[:joins]) || options[:joins]
sql << " #{expand_join_query(join)} " if join
end
def expand_join_query(*joins)
joins.flatten.map{|join|
case join
when Symbol
ref = reflections[join] or
raise ActiveRecord::ActiveRecordError, "Could not find the source association :#{join} in model #{self}"
case ref.macro
when :belongs_to
"INNER JOIN %s ON %s.%s = %s.%s" % [ref.table_name, ref.table_name, primary_key, table_name, ref.primary_key_name]
else
"INNER JOIN %s ON %s.%s = %s.%s" % [ref.table_name, ref.table_name, ref.primary_key_name, table_name, primary_key]
end
else
join.to_s
end
}.join(" ")
end
end
end
参考:http://snippets.dzone.com/posts/show/2119
我的建议是,为什么要你使用关联名称的急切加载?
尝试类似:
Effort.joins(:project_tasks, :user).select("sum(hours) as total_hours, users.full_name, project_tasks.task_name").group("users.user_id, project_tasks.task_name")
'record = Effort.sum(:hours, :joins =>:project_task, :conditions => {“project_tasks。project_id“=> project.id, ”project_tasks.task_name“=> taskname}) projdata ['values'] .push(record) end'我需要它看起来类似于此,以便我可以然后推所有的数据返回到数组 – David
然后你可以尝试在链的末尾添加类似'.collect(&:hours)'的东西(参见http://www.ruby-doc.org/core/classes/Array。 (#)html#M000247 fo collect()usage) –
不知道它是否有效,但你可能想试试这个: Effort.sum(:hours, :group => [:project_task_id,:user_id], :joins = > [:project_task,:user]) –
试试这个:
Effort.select(
"users.full_name full_name,
project_tasks.task_name task_name,
SUM(efforts.hours) total_hours").
joins(:project_task, :user).
group("users.user_id, users.full_name, project_tasks.task_name").map do |e|
puts e.full_name, e.task_name, e.total_hours
end
问题是什么? – Daan
我想将上面的SQL语句转换为RoR,以便当我将它实现为高层图时,它将显示所有用户他们的任务和他们的小时 – David
为此,您需要显示ActiveRecord模型,因为从中生成SQL。但是真正的问题是什么?究竟是什么错误?你需要更清楚一点.. – Daan