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? > >