Virtual Fields using PyDAL outside of web2py

[Jim S]

Given the following:

from pydal import DAL
from pydal.objects import Field, FieldMethod, FieldVirtual
from qlf import util
ccfg = util.Config()

db = DAL(ccfg.connect_db.dal_string, migrate=False, pool_size=5)
db.define_table('workorder',
                Field('parent_workorder', 'reference workorder'),
                Field('product'),
                Field('quantity', 'decimal(11,2)'))

db.workorder.children = Field.Virtual(lambda row: db(db.workorder.parent_workorder == row.id).select())

wo = db.workorder(248222)
print(wo.children)

...I get this when I run the code:

Traceback (most recent call last):
  File "/home/jim/dev/miscellaneous/projects/scratch/virtual_fields_test.py", line 15, in <module>
    print(wo.children)
  File "/home/jim/.local/lib/python2.7/site-packages/pydal/objects.py", line 167, in __getattr__
    raise AttributeError
AttributeError

What I'm trying to do is get a list of all child workorders for a specific workorder.

I've also tried using Field.Method and calling as a function, but get an attribute error as well.

Anyone have experience with Virtual Fields?  I haven't used them in or out of web2py yet but am interested.  Basically, I'm trying to reproduce the 'backref' behavior from SQLAlchemy.

Thanks for any help

-Jim

[Jim S]

I should also mention that I have been looking at this post, but can't see what I'm doing wrong..

https://groups.google.com/forum/?pli=1#!newtopic/web2py/web2py/PpouIgeIwX8

[Massimiliano]

Maybe you miss fieldname:

db.workorder.children = Field.Virtual(fieldname,  lambda row: db(db.workorder.parent_workorder == row.id).select())

[Jim S]

Massimiliano

Thanks for the reply.  Adding the fieldname didn't help, but pushed me to look again at the other post I'd referenced.  In it I missed adding the tablename to the row.id.  It should be row.workorder.id.  I made that change and now it works.

So, the solution is:

db.workorder.children = Field.Virtual('children', lambda row: db(db.workorder.parent_workorder == row.workorder.id).select())

Thanks for the nudge Massimiliano!

-Jim