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