Virtual fields

Virtual fields are also computed fields (as in the previous subsection) but they differ from those because they are virtual in the sense that they are not stored in the db and they are computed each time records are extracted from the database. They can be used to simplify the user’s code without using additional storage but they cannot be used for searching.

New style virtual fields (experimental)

web2py provides a new and easier way to define virtual fields and lazy virtual fields. This section is marked experimental because the APIs may still change a little from what is described here.

Here we will consider the same example as in the previous subsection. In particular we consider the following model:

db.define_table('item',
                Field('unit_price', 'double'),
                Field('quantity', 'integer'))

One can define a total_price virtual field as

db.item.total_price = Field.Virtual(lambda row: row.item.unit_price * row.item.quantity)

i.e. by simply defining a new field total_price to be a Field.Virtual. The only argument of the constructor is a function that takes a row and returns the computed values.

A virtual field defined as the one above is automatically computed for all records when the records are selected:

for row in db(db.item).select():
    print row.total_price

It is also possible to define method fields which are calculated on-demand, when called. For example:

db.item.discounted_total = \
    Field.Method(lambda row, discount=0.0:
                 row.item.unit_price * row.item.quantity * (100.0 - discount / 100))

In this case row.discounted_total is not a value but a function. The function takes the same arguments as the function passed to the Method constructor except for row which is implicit (think of it as self for objects).

The lazy field in the example above allows one to compute the total price for each item:

for row in db(db.item).select(): print row.discounted_total()

And it also allows to pass an optional discount percentage (say 15%):

for row in db(db.item).select(): print row.discounted_total(15)

Virtual and Method fields can also be defined in place when a table is defined:

db.define_table('item',
                Field('unit_price', 'double'),
                Field('quantity', 'integer'),
                Field.Virtual('total_price', lambda row: ...),
                Field.Method('discounted_total', lambda row, discount=0.0: ...))

Mind that virtual fields do not have the same attributes as regular fields (length, default, required, etc). They do not appear in the list of db.table.fields and in older versions of web2py they require a special approach to display in SQLFORM.grid and SQLFORM.smartgrid. See the discussion on grids and virtual fields in Chapter 7.

Old style virtual fields

In order to define one or more virtual fields, you can also define a container class, instantiate it and link it to a table or to a select. For example, consider the following table:

db.define_table('item',
                Field('unit_price', 'double'),
                Field('quantity', 'integer'))

One can define a total_price virtual field as

class MyVirtualFields(object):
    def total_price(self):
        return self.item.unit_price * self.item.quantity
db.item.virtualfields.append(MyVirtualFields())

Notice that each method of the class that takes a single argument (self) is a new virtual field. self refers to each one row of the select. Field values are referred by full path as in self.item.unit_price. The table is linked to the virtual fields by appending an instance of the class to the table’s virtualfields attribute.

Virtual fields can also access recursive fields as in

db.define_table('item',
                Field('unit_price', 'double'))
db.define_table('order_item',
                Field('item', 'reference item'),
                Field('quantity', 'integer'))
class MyVirtualFields(object):
    def total_price(self):
        return self.order_item.item.unit_price * self.order_item.quantity
db.order_item.virtualfields.append(MyVirtualFields())

Notice the recursive field access self.order_item.item.unit_price where self is the looping record.

They can also act on the result of a JOIN

rows = db(db.order_item.item == db.item.id).select()
class MyVirtualFields(object):
    def total_price(self):
        return self.item.unit_price * self.order_item.quantity
rows.setvirtualfields(order_item=MyVirtualFields())
for row in rows:
    print row.order_item.total_price

Notice how in this case the syntax is different. The virtual field accesses both self.item.unit_price and self.order_item.quantity which belong to the join select. The virtual field is attached to the rows of the table using the setvirtualfields method of the rows object. This method takes an arbitrary number of named arguments and can be used to set multiple virtual fields, defined in multiple classes, and attach them to multiple tables:

class MyVirtualFields1(object):
    def discounted_unit_price(self):
        return self.item.unit_price * 0.90
class MyVirtualFields2(object):
    def total_price(self):
        return self.item.unit_price * self.order_item.quantity
    def discounted_total_price(self):
        return self.item.discounted_unit_price * self.order_item.quantity
rows.setvirtualfields(item=MyVirtualFields1(),
                      order_item=MyVirtualFields2())
for row in rows:
    print row.order_item.discounted_total_price

Virtual fields can be lazy; all they need to do is return a function and access it by calling the function:

db.define_table('item',
                Field('unit_price', 'double'),
                Field('quantity', 'integer'))
class MyVirtualFields(object):
    def lazy_total_price(self):
        def lazy(self=self):
            return self.item.unit_price * self.item.quantity
        return lazy
db.item.virtualfields.append(MyVirtualFields())
for item in db(db.item).select():
    print item.lazy_total_price()

or shorter using a lambda function:

class MyVirtualFields(object):
    def lazy_total_price(self):
        return lambda self=self: self.item.unit_price * self.item.quantity