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
print rows.f
for row in rows:
print row.a, row.e
What about being able to give these a name?
This way, it works like the virtualfields that we're discussing. That way
instead of going row[e], you can go row.revenues
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
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)
-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
> > > > > > > > > > > > > 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 [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
-~----------~----~----~----~------~----~------~--~---
