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"
> >
> > > > > > > > > > >http://www.w3schools.com/sql/sql_alias.asp
> >
> > > > > > > > > > > -Thadeus
> >
> > > > > > > > > > > On Tue, Oct 27, 2009 at 3:31 PM, mdipierro <
> > > > [email protected]>
> > > > > > > > > > wrote:
> >
> > > > > > > > > > > > I am going with "virtualfields"
> >
> > > > > > > > > > > > On Oct 27, 3:19 pm, mdipierro <
> [email protected]>
> > > > wrote:
> > > > > > > > > > > > > we normally user expressions to refer to things
> like this
> >
> > > > > > > > > > > > > db(query).update(field=db.table.field+1)
> >
> > > > > > > > > > > > > How about interface?
> >
> > > > > > > > > > > > > On Oct 27, 3:05 pm, Thadeus Burgess <
> > > > [email protected]>
> > > > > > > > wrote:
> >
> > > > > > > > > > > > > > Not meta, too confusing with django stuff.
> >
> > > > > > > > > > > > > > How about expression, makes much more sense. That
> is
> > > > what it is
> > > > > > > > > > > > actually
> > > > > > > > > > > > > > referred to when talking about SQL. Access calls
> them
> > > > > > > > expressions
> > > > > > > > > > as
> > > > > > > > > > > > well.
> >
> > > > > > > > > > > > > > -Thadeus
> >
> > > > > > > > > > > > > > On Tue, Oct 27, 2009 at 2:32 PM, mdipierro <
> > > > > > > > > > [email protected]>
> > > > > > > > > > > > wrote:
> >
> > > > > > > > > > > > > > > should this thing be called meta? interface?
> > > > extension?
> >
> > > > > > > > > > > > > > > On Oct 27, 2:26 pm, mdipierro <
> > > > [email protected]>
> > > > > > > > wrote:
> > > > > > > > > > > > > > > > Simple example:
> >
> > > > > > > > > > > > > > > > db=DAL('sqlite://test')
> > > > > > > > > > > > > > > > db.define_table('purchase',
> > > > > > > > > > > > > > > > Field('item'),
> > > > > > > > > > > > > > > > Field('unit_price','double'),
> > > > > > > > > > > > > > > > Field('quantity','integer'))
> >
> > > > db.purchase.insert(item='Box',unit_price=15,quantity=3)
> > > > > > > > > > > > > > > > rows=db().select(db.purchase.ALL)
> >
> > > > > > > > > > > > > > > > class purchase_meta:
> > > > > > > > > > > > > > > > _tablename='purchase'
> > > > > > > > > > > > > > > > def __init__(self,tax):
> > > > > > > > > > > > > > > > self.tax=tax
> > > > > > > > > > > > > > > > def revenues(self):
> > > > > > > > > > > > > > > > return
> >
> > > > self.purchase.unit_price*self.purchase.quantity*self.tax
> >
> > > > > > > > > > > > > > > > rows.meta=purchase_meta(1.07)
> >
> > > > > > > > > > > > > > > > for row in rows:
> > > > > > > > > > > > > > > > print row.item,
> >
> > > > > > > > row.unit_price,'*',row.quantity,'*',row.tax,'=',row.revenues
> >
> > > > > > > > > > > > > > > > More complex example:
> >
> > > > > > > > > > > > > > > > db.define_table('a',Field('n','integer'))
> >
> > > > db.define_table('b',Field('n','integer'),Field('a',db.a))
> > > > > > > > > > > > > > > > id = db.a.insert(n=4)
> > > > > > > > > > > > > > > > for i in range(3,5): db.b.insert(n=i,a=id)
> > > > > > > > > > > > > > > > rows=db(db.b.a==db.a.id).select() ### join
> >
> > > > > > > > > > > > > > > > class products:
> > > > > > > > > > > > > > > > _tablename='c'
> > > > > > > > > > > > > > > > def n(self): return self.a.n*self.b.n
> >
> > > > > > > > > > > > > > > > rows.meta=products()
> > > > > > > > > > > > > > > > for row in rows:
> > > > > > > > > > > > > > > > print row.a.n,'*',row.b.n,'=' row.c.n
> >
> > > > > > > > > > > > > > > > Any suggestions on improving the syntax?
> Django can
> > > > do the
> > > > > > > > same
> > > > > > > > > > but
> >
> > ...
> >
> > 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
-~----------~----~----~----~------~----~------~--~---
