Re: [web2py] Doing a select in a Virtual Field

[Tom Coetser]

On Monday 15 August 2011 19:47:09 pbreit wrote:
> I think you might have to dereference r. Something like r[0].odo since
> queries return a set even if there's only one element.

From my tests, the .first() in the query:

> r = db(q).select(db.trip.odo, orderby=o, limitby=(0,1)).first()

seems to return only one Row. This is confirmed where r is returned by the 
distance function in a tuple:

> return (d, self.trip.odo, r)  # Return extra info to see problem

and then printed out in the test loop:

> 2011-01-03 | 1300 | (0, 1200, )

Here the tuple at the end is the return value from the distance function, with 
r the row selected in the virtual function, showing thaqt the selected row odo 
value is 1200. The second value in the tuple is supposed to be the trip.odo 
value of the record passed into the virtual function (should be 1300 in this 
case shown as the second field printed), but inside the virtual function, 
after doing a select for the previous row, *self* seems to be *replaced* by 
the select result.

It's almost like a buffer is being overwritten when doing a select in the 
virtual field function which causes the original record passed into the 
function to be lost.

Thanks for your reply though.


/Tom

[web2py] Doing a select in a Virtual Field

[Tom Coetser]

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.date0).select():
print n.date, '|', n.odo, '|', n.distance
   : 
   : 
2011-01-01 | 1100 | (None, 1100, None)
2011-01-02 | 1200 | (0, 1100, )
2011-01-03 | 1300 | (0, 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