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 <[email protected]> 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 [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 -~----------~----~----~----~------~----~------~--~---
