Yes, but I want to be able to apply the virtualfield to every row, like how SUM or AVG would work.
In this example, I would print a spreadsheet view of all sales, and I would want at the very bottom of the report, GROSS TOTAL SALES w/ TAX. For this to happen, I would need to SUM my virtualfield VF_total.total -Thadeus On Tue, Oct 27, 2009 at 10:54 PM, mdipierro <[email protected]> wrote: > > If I understand. You can do this already: > > db.define_table('products', Field('name'), Field('price', 'double')) > db.define_tables('sales', Field('product', db.products), Field > ('quantity','integer')) > > qry_product_sales = (db.sales.id_product == db.products.id) & > (db.products.id > 0) > > class VF_total: > _tablename = 'calcd' > def __init__(self, tax): self.tax = tax > > def total_pre_tax(self): return (self.sales.product.price > *self.sales.quantity) > def sales_tax(self): return self.total() - self.total_pre_tax() > def total(self): return self.total_pre_tax() * self.tax > > ... #later in controllers > > all_sales = db(qry_product_sales).select() > > all_sales.virtualfields = VF_total(1.07) > > print "Name, Price, Quantity, SubTotal, Tax, Total" > for sale in all_sales: > print sale.sales.product.name, sale.sales.product.price, > sale.sales.quantity, sale.calcd.total_pre_tax, sale.calcd.sales_tax, > sale.calcd.total > > On Oct 27, 10:18 pm, Thadeus Burgess <[email protected]> wrote: > > I just finished watching your video about virtualfields. > > > > Its a toss up between the two. > > > > The one thing I really like about the class structure, is being able to > pass > > defaults to it (like tax). > > > > Another thing that I really like about classes, is that when you specify > > _tablename to something else, it creates a virtual table. This way a > virtual > > table can be created, with access to all of the functions in the class. > > > > Everything that I was worried about being able to do, can be accomplished > > with virtualfields in classes. > > > > I think using a dictionary instead would be more restrictive. I really > like > > what you settled on in the video. > > > > The only thing that *might* come up in the future, is what if you wanted > > multiple classes passed to your virtual field, which may or may not ever > be > > needed. > > > > I can see a layout in db.py such as > > > > db.define_table('products', Field('name'), Field('price', 'double')) > > db.define_tables('sales', Field('product', db.products), > Field('quantity', > > 'integer')) > > > > qry_product_sales = (db.sales.id_product == db.products.id) & ( > > db.products.id > 0) > > > > class VF_total: > > _tablename = 'calcd' > > def __init__(self, tax): self.tax = tax > > > > def total_pre_tax: return (self.sales.product.price * > > self.sales.quantity) > > def sales_tax: return self.total - self.total_pre_tax > > def total: return self.total_pre_tax * self.tax > > > > ... #later in controllers > > > > all_sales = db(qry_product_sales) > > > > all_sales.virtualfields = VF_total(1.07) > > > > print "Name, Price, Quantity, SubTotal, Tax, Total" > > for sale in all_sales: > > print sale.sales.product.name, sale.sales.product.price, > > sale.sales.quantity, sale.calcd.total_pre_tax, sale.calcd.sales_tax, > > sale.calcd.total > > > > ### Now this is the only thing that I think is missing, aggregates on the > > rows object. These would be columns that don't necissarily need to be on > all > > rows. > > print "Total Gross Sales", all_sales.calcd.total.sum() > > print "Average Sale", all_sales.calcd.total.avg() > > > > -Thadeus > > > > On Tue, Oct 27, 2009 at 9:58 PM, mdipierro <[email protected]> > wrote: > > > > > One thing I do not like: > > > > > right now we are using a class/object to define the virtualfields but > > > you are suggesting a dictionary to name aggregates. Should we use a > > > dictionary for virtualfields too, for example? > > > > > rows.virtualfields={'total':(lambda self: > > > self.sale.unit_price*self.sale.quantity)} > > > > > Is this better than? > > > > > class test: > > > def total(self): > > > return self.sale.unit_price*self.sale.quantity > > > rows.virtualfields=test() > > > > > On Oct 27, 9:52 pm, mdipierro <[email protected]> wrote: > > > > On Oct 27, 9:23 pm, Thadeus Burgess <[email protected]> wrote: > > > > > > > On the same track that I was thinking. However this does not work > like > > > I > > > > > would expect. > > > > > > > e = (db.a.b * 10) > > > > > f = (db.a.b.max() * db.a.b.min()) > > > > > rows = db(db.a.id > 0).select(db.a.ALL, e, f) > > > > > > > return dict(rows=SQLTABLE(rows)) > > > > > > > For something like the above, f would only have one row, and e > would be > > > for > > > > > each row. So what would be nice to work like > > > > > > you cannot mis db.a.ALL and expressions like f without doing groupby. > > > > > > > print rows.f > > > > > > > for row in rows: > > > > > print row.a, row.e > > > > > > > What about being able to give these a name? > > > > > > It would be > > > > > > print row.a, row[e] > > > > > > The first is a table, the second is a formula > > > > > > > This way, it works like the virtualfields that we're discussing. > That > > > way > > > > > instead of going row[e], you can go row.revenues > > > > > > It is more complicated than that. This would work only there were no > > > > joins. The current approach has the advantage that does not break > > > > joins. > > > > > > > However revenues is an Expression like db.a.b * 10. > > > > > > > So in my ealier example for the aliases would change. Make it a > python > > > > > dictionary of Expression objects. with the key being the fieldname. > > > > > > > Fields with only one row (SUM AVG etc..) would be accessible from > > > within the > > > > > rows object, fields with multiple rows, within the row object > > > > > > > db.define_table('a', Field('b', 'double')) > > > > > > > for i in range(2,6): > > > > > db.a.insert(b=i) > > > > > > > expressions = { > > > > > 'taxes': db.a.b * 1.07, > > > > > 'max_min': db.a.b.min() * db.a.b.max(), > > > > > 'total': db.a.b.sum() > > > > > > > } > > > > > > > db().select(db.a.ALL, exp=expressions) > > > > > > > print rows.max_min > > > > > print rows.total > > > > > > > for row in rows: > > > > > print "amt: ", row.b, " || Taxes: ", row.taxes > > > > > > Let me think about this. > > > > > > > Or to keep from clashing table namespace.... > > > > > > > row.virtualfields.taxes > > > > > > > All in all, I like the implementation, but it would be nice to be > able > > > to > > > > > name the Expression object (like you can the virtualfields) > > > > > > Right now you can but you have to use virtualfields to name the > > > > expressions. > > > > > > > -Thadeus > > > > > > > On Tue, Oct 27, 2009 at 6:28 PM, mdipierro < > [email protected]> > > > wrote: > > > > > > > > In trunk now: > > > > > > > > db.define_table('a',Field('b','double')) > > > > > > e=(db.a.b.max()-3)*(db.a.b.min()+5) > > > > > > rows = db().select(e) > > > > > > row=rows.first() > > > > > > print row._extra[e] > > > > > > > > can now be written as: > > > > > > > > db.define_table('a',Field('b','double')) > > > > > > e=(db.a.b.max()-3)*(db.a.b.min()+5) > > > > > > rows = db().select(e) > > > > > > row=rows.first() > > > > > > print row[e] > > > > > > > > I am not sure I completely like the implementation of this. I > need to > > > > > > sleep on this. Please send me comments. > > > > > > > > Massimo > > > > > > > > On Oct 27, 5:59 pm, mdipierro <[email protected]> wrote: > > > > > > > _extra literally means anything returned by the query that is > not a > > > > > > > table field. > > > > > > > > > On Oct 27, 5:55 pm, mdipierro <[email protected]> wrote: > > > > > > > > > > I do not like the _extra either and I am thinking of a way to > get > > > rid > > > > > > > > of it but it is all but chaotic. > > > > > > > > Consider this example > > > > > > > > > > db.define_table('a',Field('b','double')) > > > > > > > > e=(db.a.b.max()-3)*(db.a.b.min()+5) > > > > > > > > rows = db().select(e) > > > > > > > > row=rows.first() > > > > > > > > print row._extra[e] > > > > > > > > > > the _extra prevents conflicts with table names and field > names > > > and > > > > > > > > allows the key to be any complex expressions. A simpler > notation > > > would > > > > > > > > not allow that. > > > > > > > > > > I am considering implementing row(e) to be the same as > > > row._extra[e]. > > > > > > > > > > Massimo > > > > > > > > > > On Oct 27, 4:50 pm, Thadeus Burgess <[email protected]> > > > wrote: > > > > > > > > > > > I really need to read that thing :) > > > > > > > > > > > However, that syntax is chaotic. That row._extra syntax > makes > > > me > > > > > > cringe. If > > > > > > > > > it works it works, but I think it could be ***better***. > Why > > > can it > > > > > > not be > > > > > > > > > something like. > > > > > > > > > > > rows = db(query).select(sum(field), avg(field)) # I just > > > psuedocoded > > > > > > this. > > > > > > > > > Like i usually do in emails :) > > > > > > > > > > > print "Sum", rows.field.sum > > > > > > > > > print "Avg", rows.field.avg > > > > > > > > > > > for r in rows: > > > > > > > > > print r.table.field > > > > > > > > > > > -Thadeus > > > > > > > > > > > On Tue, Oct 27, 2009 at 4:42 PM, mdipierro < > > > [email protected]> > > > > > > wrote: > > > > > > > > > > > > On Oct 27, 4:35 pm, Thadeus Burgess < > [email protected]> > > > wrote: > > > > > > > > > > > Ok, I was about to stand my ground, however I now agree > > > with you. > > > > > > > > > > > > > virtualfields is fine to me, as long as our DAL gets > > > support for > > > > > > native > > > > > > > > > > SQL > > > > > > > > > > > aggregates (liek SUM, AVG, etc). > > > > > > > > > > > > The DAL does that already. Just not all of them. > > > > > > > > > > It did so for one year. It is in the manual. > > > > > > > > > > > > for row in > > > db(...).select(db.table.field,db.table.otherfield.sum > > > > > > > > > > (),groupby=db.table.field): > > > > > > > > > > print row.table.field, > > > row._extra[db.table.otherfield.sum()] > > > > > > > > > > > > db.table.field.sum() > > > > > > > > > > db.table.field.max() > > > > > > > > > > db.table.field.min() > > > > > > > > > > db.table.field.count() > > > > > > > > > > > > > And when the DAL gets that, it leaves open the option > to > > > assign > > > > > > true > > > > > > > > > > aliases > > > > > > > > > > > that are tied into the SQL query. > > > > > > > > > > > > > aliases = { > > > > > > > > > > > 'num_items': ['count', '*'], > > > > > > > > > > > 'total_price': ['sum', 'item.price'], > > > > > > > > > > > 'average_price': ['avg', 'item.price'],} > > > > > > > > > > > > > db(db.invoice.id == > > > > > > request.vars.id_invoice).select(alias=aliases) > > > > > > > > > > > > > So i'm okay with virtualfields now :P > > > > > > > > > > > > > -Thadeus > > > > > > > > > > > > > On Tue, Oct 27, 2009 at 4:12 PM, mdipierro < > > > > > > [email protected]> > > > > > > > > > > wrote: > > > > > > > > > > > > > > No no. We are not doing that. > > > > > > > > > > > > > > The new virtualfields are compute by web2py, not by > the > > > > > > database. > > > > > > > > > > > > > > Massimo > > > > > > > > > > > > > > On Oct 27, 3:55 pm, Thadeus Burgess < > > > [email protected]> > > > > > > wrote: > > > > > > > > > > > > > Basically what we are doing is > > > > > > > > > > > > > > > SELECT COUNT(*) AS "Number of Orders", > > > > > > > > > > > > > SUM(quantity)AS "Total Number of Items Purchased", > > > > > > > > > > > > > AVG(quantity)AS "Average Number of Items Purchased" > > > > > > > > > > > > > > > FROM orders; > > > > > > > > > > > > > > > What is the correct terminology for AS statement? > Some > > > > > > research > > > > > > > > > > suggest > > > > > > > > > > > > > ALIAS is the most accurate term. > > > > > > > > > > > > > > > I think we should use "alias" or "aliases" > > > > ... > > > > read more ยป > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
