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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
