web2py virtualfields as an ORM (an sqlalchemy approach)
One of the finer new features in web2py is the ability to declare what is called a "virtual field".
A virtual field is a function that gets called/calculated when the web2py DAL performs a select on the database tables.
The neat thing about virtual fields, is it allows us to give the DAL ORM functionalities like you would find in django and sqlalchemy.
db.define_table('sales',
Field('item'),
Field('item_total', 'double'),
Field('tax_percentage', 'double'),
Field('trans_date', 'datetime'),
)
class Sales():
def total(self):
return self.sales.item_total * self.sales.tax_percentage
def tax(self):
return self.total() - self.sales.item_total
db.sales.virtualfields.append(Sales())
2009_sales = db(
(db.sales.trans_date <= "2009-12-31")
(db.sales.trans_date >= "2009-01-01")
).select(orderby=~db.sales.trans_date)
for sale in 2009_sales:
print "Item: ", sale.item
print "Price: ", sale.item_total
print "Tax: ", sale.tax # Notice, we are getting from our virtualfield class we defined!
print "Total: ", sale.total
Sometimes you might want a lazy virtualfield that only gets calculated when you call it. A blog post permalink would be a situation where this would be beneficial.
db.define_table('post',
Field('title'),
Field('content')
)
class Post():
def permalink(self):
def lzy():
# Define an inner function that will perform our action
return URL(r=request, c='default', f='view', args=self.post.title.replace(' ', '-'))
# We are only returning a reference to the function, this will get called later!
return lzy
db.post.virtualfields.append(Post())
for row in db(db.post.id > 0):
print row.title
print row.permalink() # Notice, we have to call the fucntion!
print content