Aasish Sharma Aasish Sharma - 3 months ago 13
Ruby Question

To display time spent by user on all tasks daily

I want to display how much time a single user spends on all tasks in a day.

Currently I'm using this query to display the time spent by all the users on all tasks daily:

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


These are the Schema Information of models I have used.

#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

This is how I was able to get the desired result

u_id = params[: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")

It will create a HASH.