Hi All,
I'm new to web2py and am trying to get my mind around how virtual fields work.
Given the following table:
--------------------
db.define_table("trip",
Field("date", "date"),
Field("odo", "integer")
)
--------------------
which represents a trip taken by car on a specific date and the odometer
reading at the end of each trip:
--------------------
trips = [
{'date': datetime.date(2011, 1, 1), 'odo': 1100},
{'date': datetime.date(2011, 1, 2), 'odo': 1200},
{'date': datetime.date(2011, 1, 3), 'odo': 1300}
]
db.trip.bulk_insert(trips)
--------------------
I am trying to add a virtual field to calculate the distance traveled for each
trip, such that:
distance = (odo of this trip record) - (odo of previous trip record)
I add the following virtual fields class and associate it with the table:
--------------------
class tripVirtualFields(object):
def distance(self):
q = (db.trip.date<self.trip.date) # Query for previous record
o = (~db.trip.date, ~db.trip.odo) # Order descending by date
r = db(q).select(db.trip.odo, orderby=o, limitby=(0,1)).first()
d = (self.trip.odo - r.odo) if r else None # Calc trip distance
return (d, self.trip.odo, r) # Return extra info to see problem
db.trip.virtualfields.append(tripVirtualFields())
--------------------
Then when I test this in ipyhton:
--------------------
In [17]: for n in db(db.trip.id>0).select():
print n.date, '|', n.odo, '|', n.distance
....:
....:
2011-01-01 | 1100 | (None, 1100, None)
2011-01-02 | 1200 | (0, 1100, <Row {'odo': 1100}>)
2011-01-03 | 1300 | (0, 1200, <Row {'odo': 1200}>)
--------------------
Notice that for the second and 3rd rows the the calculated distance is always
0 because it seems that the 'self' row passed in to the distance() virtual
field function gets 'overwritten' with the row returned with the select in the
same function.
The question is: can one do a new select inside virtual field function to get
data back from another table or from the same table the virtual function acts
upon? If not, any other ideas on how to implement this type of requirement in
a web2py app?
Any help would be much appreciated.
Thanks,
Tom
