Another DAL challenge..... accepted!
assuming the model:
db.define_table('tasks',
Field('name')
)
db.define_table('periods',
Field('task_id', db.tasks),
Field('start_time', 'datetime'),
Field('end_time', 'datetime'),
)
You'd like to calculate the total duration (let's say in seconds) of every
period, and sum that total duration by task_id. Instead of visualizing the
task_id, you want task.name.
Instead of blowing the full solution, let's take it one step at a time,
hopefully enjoying the logic that ties your goal, your brain, the database
and the DAL abstraction....
for every "period", duration is calculated as:
duration = db.periods.end_time.seconds() - db.periods.start_time.seconds()
now, get a list of all "durations"...
result = db(db.periods.id>0).select(db.periods.id, duration)
But, you want the SUM of this durations, grouped by task_id.....
let's rewrite "duration" as the SUM of it
duration = (db.periods.end_time.seconds() -
db.periods.start_time.seconds()).sum()
and the result, with task_id
result = db(db.periods.id>0).select(db.periods.task_id, durations,
groupby=db.periods.task_id)
Now, slip into it task.name instead of the id, joining the two tables....
the definition of duration remains the same
duration = (db.periods.end_time.seconds() -
db.periods.start_time.seconds()).sum()
result = db(db.periods.task_id ==
db.tasks.id).select(db.tasks.name,duration, groupby=db.tasks.name)
Everything clear ??
NB: a little caveat.... with compound expressions like sum() or this kind
of calculations, looping the results can't be done in the usual way, like:
for row in result:
print row.tasks.name, row.duration
this is going to raise an exception because duration is not a proper column
of a table....
You can use the dictionary notation to solve the "problem":
for row in result:
print row.tasks.name, row[duration]
Il giorno lunedì 19 marzo 2012 20:00:42 UTC+1, backseat ha scritto:
>
> I need to run a reasonably complex query, and I'm wondering whether it
> would be better to code it in SQL, or should I try to get the DAL to
> provide the data (or maybe there's another way). There's no right or wrong
> answer, but I'd welcome opinion on the best approach.
>
> Simply put, consider two tables, "tasks" and "periods". As well as an id
> field, tasks has a 'name' field too. Each task has multiple periods (of
> work) associated with it, and the periods table has id, date, start_time,
> end_time (and other fields). What I want to do is produce a summary that
> lists:
>
> task_name total_time
>
> ...for each task. That is, for each period, find the duration by
> subtracting the start_time from the end_time, and total all durations for
> each task in turn.
>
> I can write this in SQL, but then I lose the advantages of the DAL, as
> well as potentially losing database portability. I'm not even sure if this
> is doable in the DAL.
>
> What would be the best approach?
> --
> "You can have everything in life you want if you help enough other people
> get what they want" - Zig Ziglar.
>
> Who did you help today?
>
>
