Need help turning a SQL query into an ActiveRecord query

I have the following SQL code:

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

Which outputs all users, their tasks and their hours. What I'm now trying to do is convert this to a Rails' ActiveRecord query.

I am trying to make it look similar to what I have done below but cannot seem to get my logic right.

    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 } )             
        proj开发者_运维百科data[ 'values' ].push( record )
      end

      @data.push( projdata )
    end
  end
end 

Added image link

Link to image

The link illustrates a graph. What I need to do is convert my SQL statement into an activeRecord query which will display it like my other graph just as I supplied.

---

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

But, I have one doubt, how can you get the "hours" field without adding it's on the grouping section. So, it's better, you can add the hours too in grouping section.

But, You should make some additional changes in the following file

vendor/plugins/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

Reference: http://snippets.dzone.com/posts/show/2119

My suggestion is,why should you use the eager loading with association names?.

---

Try this:

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 

---

try something like :

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