DAL can abstract some deal of queries but not such complex ones. Either you
use executesql or you create a view and access it inside web2py as a "fake
table" defining it with migrate=False.
PS: unless for some weird requirements, that select is incredibly expensive.
You'd be better off fetching the task, the worker associated and the
scheduler_run record with the max id with scheduler_task == id of the task
(it'll do the max with the PK of the scheduler_run table and the groupby on
the FK).
On Friday, February 15, 2013 2:21:39 PM UTC+1, operE Aperte wrote:
>
> Hi all,
>
> I need to merge scheduler task, worker and run tables to get in one Rows
> object all the tasks, their assigned workers and the last run for each task.
> With sql I can achieve this with something like:
> rows_sch=db.executesql("SELECT t.*,w.*,r.*
> FROM SCHEDULER_TASK AS t
> LEFT JOIN SCHEDULER_WORKER AS w ON
> t.ASSIGNED_WORKER_NAME=w.WORKER_NAME
> LEFT JOIN (SELECT * FROM SCHEDULER_RUN AS a
> NATURAL LEFT JOIN
> (SELECT SCHEDULER_TASK,MAX(START_TIME)
> AS START_TIME_MAX FROM SCHEDULER_RUN
> GROUP BY SCHEDULER_TASK) AS b
> WHERE START_TIME=START_TIME_MAX) r
> ON t.ID=r.SCHEDULER_TASK")
>
> but I can't get this working with DAL, mostly because it seems I can't use
> the select left option with groupby and having.
> Any suggestion?
>
>
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
