显示用户每天在所有任务上花费的时间

Question

我想显示单个用户一天中在所有任务上花费的时间。

目前，我使用此查询来显示所有用户每天在所有任务上花费的时间：

TaskTimeTracker.group("year(created_at)").group("month(created_at)").group("day(created_at)").sum("time_spent")

这些是我使用的模型的模式信息。

#Table name: tasks
#id              :integer          not null, primary key
#name            :string(255)
#description     :text(65535)
#due_date        :string(255)
#status          :integer
#priority        :integer
#project_id      :integer
#user_id         :integer
#created_at      :datetime         not null
#updated_at      :datetime         not null
#estimated_time  :datetime
#start_time      :datetime
#completion_time :datetime
#parent_task_id  :integer
#task_type       :string(255)
#author_id       :integer
#slug            :string(255)
#Indexes
#index_tasks_on_project_id  (project_id)
#index_tasks_on_slug        (slug) UNIQUE
#index_tasks_on_user_id     (user_id)
#Table name: task_time_trackers
#id          :integer          not null, primary key
#description :text(65535)
#time_spent  :float(24)
#created_at  :datetime         not null
#updated_at  :datetime         not null
#task_id     :integer
#created_by  :string(255)
#Indexes
#index_task_time_trackers_on_task_id  (task_id)

class TaskTimeTracker < ActiveRecord::Base
    belongs_to :task
    validates :description,presence:true
    validates :time_spent,presence:true
end
class Task < ActiveRecord::Base
  include PublicActivity::Model
  attr_accessor :estimated_time_field, :prev_user_id
  validate :due_date_validation
  extend FriendlyId
  friendly_id :task_type, use: [:slugged,:finders]
  belongs_to :project
  belongs_to :user
  has_many :worked_tasks, dependent: :destroy
  has_many :comments, dependent: :destroy
  has_many :logs, dependent: :destroy
  has_and_belongs_to_many :tags
  has_many :subtasks, class_name: "Task", :foreign_key => :parent_task_id
  belongs_to :parent_task, class_name: "Task" 
  has_many :task_time_trackers, dependent: :destroy
end

Answer 1

这就是我能够获得预期结果的方式

u_id = 参数[：u_id]

TaskTimeTracker.joins(:task).where( task_time_trackers: { user_id: u_id}).where("(task_time_trackers.created_at BETWEEN ? AND ?)",Date.current.beginning_of_month, Date.current.end_of_month ).group("task_time_trackers.task_id").group("DAY(task_time_trackers.created_at)").sum("time_spent")

它将创建一个哈希。