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;
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’
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.
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 W. 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.