I have a time tracking app where (pseudo-code) Client has_many Task has_many TimeRecord TimeRecord start_at, :datetime end_at, :datetime What I''m trying to do (and I have done using find_by_sql in the past) is get a sum of the timespan given by ended_at - begun_at for each client. Is it naive to imagine that AR''s calculations will span relations to provide a grouped result? The SQL I''m feeding to find_by_sql comes out something like this: SELECT client_id, task_id, project_id, clients.client_name, tasks.description, time_records.start_at, (SUM(TIME_TO_SEC(time_records.end_at) - time_to_sec(time_records.start_at)) / 60) AS total_client_minutes, (SUM(TIME_TO_SEC(time_records.end_at) - time_to_sec(time_records.start_at)) / 3600) AS total_client_hours FROM clients, tasks, time_records WHERE tasks.client_id = clients.id AND time_records.task_id = tasks.id GROUP BY client_id, project_id, task_id WITH ROLLUP; -- View this message in context: http://www.nabble.com/Calculations-across-multiple-tables-tf1951400.html#a5351226 Sent from the RubyOnRails Users forum at Nabble.com.
On Sun, 2006-07-16 at 11:24 -0700, s.ross wrote:> I have a time tracking app where (pseudo-code) > > Client has_many Task has_many TimeRecord > > TimeRecord > start_at, :datetime > end_at, :datetime > > What I''m trying to do (and I have done using find_by_sql in the past) is get > a sum of the timespan given by ended_at - begun_at for each client. > > Is it naive to imagine that AR''s calculations will span relations to provide > a grouped result? > > The SQL I''m feeding to find_by_sql comes out something like this: > > SELECT client_id, task_id, project_id, clients.client_name, > tasks.description, > time_records.start_at, > (SUM(TIME_TO_SEC(time_records.end_at) - > time_to_sec(time_records.start_at)) / 60) AS total_client_minutes, > (SUM(TIME_TO_SEC(time_records.end_at) - > time_to_sec(time_records.start_at)) / 3600) AS total_client_hours > FROM clients, tasks, time_records > WHERE tasks.client_id = clients.id AND time_records.task_id = tasks.id > GROUP BY client_id, project_id, task_id WITH ROLLUP;---- I''d be interested in others answers to your question since you may teach me a new way but I recently finished a punch clock module in my application and here''s how I handled it. I have a column called pair_id and when ''clocking in'' - a new pair_id is incremented and saved with the entry. When ''clocking out'', this same pair_id is written to the record and I then use the pair_id to match the ''clock out'' time to the ''clock in'' time and do my math there. I also have taken pains to prevent the user from creating a ''clock in'' if their last entry was a ''clock in'' and likewise, a user is prevented from creating a ''clock out'' record if their last entry was a ''clock out'' Craig
The wrinkle in this app is that any given task can have multiple time records. E.g.: Task id=1 / Did some coding on xyz, inc site start 10:00 stop 10:15 when the phone rang Task id=2 / Answered the phone from abc, inc start 10:15 stop 10:21 Task id=1 Did some coding on xyz, inc site start 10:22 stop 14:48 Because Task id 1 was interrupted, it has multiple time records, hence the odd requirement for calculations across relations. -- View this message in context: http://www.nabble.com/Calculations-across-multiple-tables-tf1951400.html#a5351722 Sent from the RubyOnRails Users forum at Nabble.com.
On Sun, 2006-07-16 at 12:25 -0700, s.ross wrote:> The wrinkle in this app is that any given task can have multiple time > records. E.g.: > > Task id=1 / Did some coding on xyz, inc site > start 10:00 stop 10:15 when the phone rang > > Task id=2 / Answered the phone from abc, inc > start 10:15 stop 10:21 > > Task id=1 Did some coding on xyz, inc site > start 10:22 stop 14:48 > > Because Task id 1 was interrupted, it has multiple time records, hence the > odd requirement for calculations across relations.---- perhaps in your mind...in my mind, only one time clock could be open anyway so when I started a new clock on task id=2, I would expect it to put a stop on task id=1 (or any open task) so in my mind, that really isn''t different but again, I am interested in how you solve it because it may be better than my way. Craig
The philisophical problem definition notwithstanding, I agree the question is whether AR has some slick capability for Rubyizing this kind of filtered summarizing. As I said, it can be done using SQL aggregations, but then we''re trying to to use SQL if we can abstract it, right? FWIW, the rationale behind reusing task id''s is that there are a smaller number of discrete tasks than there are periodic activities for them. Tasks have to be interruptable to be useful to me. Yes, they stop cold when they are interrupted, but they must resume and my device for resuming a task is simply to add a time record that describes the start and end of the new activity. Steve Craig White wrote:> > perhaps in your mind...in my mind, only one time clock could be open > anyway so when I started a new clock on task id=2, I would expect it to > put a stop on task id=1 (or any open task) so in my mind, that really > isn''t different but again, I am interested in how you solve it because > it may be better than my way. > > Craig >-- View this message in context: http://www.nabble.com/Calculations-across-multiple-tables-tf1951400.html#a5352534 Sent from the RubyOnRails Users forum at Nabble.com.