I think you want:
op1 = db((db.WoTtHt.hourtype==db.HourType.id)&(db.WoTtHt.workorder==2)
&
(db.WoTtHt.valid==True)).select(
db.HourType.code,db.TaskType.code,left=db.TaskType.on
(db.WoTtHt.tasktype==db.TaskType.id))
Massimo
On Jun 2, 3:59 am, Mariano Mara <mariano.m...@gmail.com> wrote:
> Good night everyone.
>
> Sorry to bother you guys with this but I'm totally stuck.
>
> I have the following tables defined:
>
> db.define_table("TaskType", db.Field("code", type='string', length=3,
> required=True, notnull=True,
> unique=True),
> db.Field("descrip", type='string',
> required=True,
> notnull=True, length=600))
> db.define_table("HourType", db.Field("code", type='string', length=3,
> required=True, notnull=True,
> unique=True),
> db.Field("descrip", type='string',
> required=True,
> notnull=True, length=600))
> db.define_table("WoTtHt", db.Field("workorder", type='integer',
> notnull=True),
> db.Field("tasktype", db.TaskType),
> db.Field("hourtype", db.HourType,
> notnull=True),
> db.Field("valid", type='boolean',
> required=True,
> notnull=True, default=True))
> db.TaskType.insert(code='ADM', descrip='Administration')
> db.TaskType.insert(code='SAL', descrip='Sales')
> db.HourType.insert(code='LUN', descrip='Lunch')
> db.HourType.insert(code='WOR', descrip='Working')
> db.WoTtHt.insert(workorder=1, tasktype=1, hourtype=1, valid=True)
> db.WoTtHt.insert(workorder=1, tasktype=2, hourtype=2, valid=True)
> db.WoTtHt.insert(workorder=2, hourtype=1, valid=True)
> db.WoTtHt.insert(workorder=2, tasktype=2, hourtype=2, valid=True)
>
> As you can see, HourType is always present and TaskType not always. I
> want to retrieve the HourType and TaskType codes for a given WorkOrder
> when the combination is valid:
>
> The following sql gives the right anwer:
> SELECT HourType.code, TaskType.code FROM HourType, WoTtHt LEFT OUTER
> JOIN TaskType ON WoTtHt.tasktype=TaskType.id WHERE
> WoTtHt.hourtype=HourType.id and WoTtHt.workorder=2 and
> WoTtHt.valid='T';
>
> however I can't produce a similar result with DAL:
> for example:
> op1 = db((db.WoTtHt.hourtype==db.HourType.id)&(db.WoTtHt.workorder==2)&
> (db.WoTtHt.valid==True)).select(
> db.HourType.code,db.TaskType.code,left=db.WoTtHt.on
> (db.WoTtHt.tasktype==db.TaskType.id))
>
> return rows when tasktype is not null (workorder 1) but it gets
> nothing when tasktype is null.
>
> The sql I get with db._lastsql isn't quite right (at least compared
> with the sql I know it works):
> "SELECT HourType.code, TaskType.code FROM HourType, TaskType LEFT
> OUTER JOIN WoTtHt ON WoTtHt.tasktype=TaskType.id WHERE
> ((WoTtHt.hourtype=HourType.id AND WoTtHt.workorder=2) AND
> WoTtHt.valid='T');"
>
> I tried several combinations to no avail (it seems the sql changes if
> I change the criteria order in the filter, too).
> I'm pretty sure it's a silly mistake but if somebody can give a tip on
> this, I will appreciate it.
>
> Mariano.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
