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
-~----------~----~----~----~------~----~------~--~---
