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 <mdipie...@cs.depaul.edu> wrote:
>
>
>
> On Oct 27, 4:35 pm, Thadeus Burgess <thade...@thadeusb.com> 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 <mdipie...@cs.depaul.edu>
> 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 <thade...@thadeusb.com> 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 <mdipie...@cs.depaul.edu>
> > > wrote:
> >
> > > > > I am going with "virtualfields"
> >
> > > > > On Oct 27, 3:19 pm, mdipierro <mdipie...@cs.depaul.edu> 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 <thade...@thadeusb.com>
> 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 <
> > > mdipie...@cs.depaul.edu>
> > > > > wrote:
> >
> > > > > > > > should this thing be called meta? interface? extension?
> >
> > > > > > > > On Oct 27, 2:26 pm, mdipierro <mdipie...@cs.depaul.edu>
> 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
> > > > > > > > > only at the level of the individual table. web2py can now
> to it
> > > for
> > > > > > > > > joins!
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
