I am not convinced that
rows = db().select(db.table.field1.sum() as foo)
+ {{=row.foo}}
is any simpler than
foo = db.table.field1.sum()
rows = db().select(foo)
+ {{=row[foo]}}
The latter has the advantage that does not generate conflicts at the
DB level.
As far as I know the book is almost ready.
Massimo
On Feb 27, 5:33 pm, Paul <paul.robinson...@gmail.com> wrote:
> OK, I can get this to work in the view now, but it seems to be overly
> complicated, I have one dal statement to generate some rows, but if I
> want lots of sums, mins and max etc I then need to pass all these
> additional objects via the dict to the view!, when surely all this
> data should be just part of the result set from the sql (and therefore
> available by iterating over the 'rows' object), Web2py is great for
> some things but this part seems a bit 'cluttered', I expected the
> syntax to be cleaner/simpler e.g.
>
> rows = db().select(db.table.field1.sum() as foo, db.table.field2.sum()
> as bar)
> return dict(rows=rows)
> ...
> ...
> {{for row in rows:}}
> {{=row.foo}} {{=row.bar}}
>
> p.s. Any idea when the Packt book will be published?, mines been on
> pre-order for 3 months now.....
>
> On Feb 27, 9:12 pm, Massimo Di Pierro <massimo.dipie...@gmail.com>
> wrote:
>
>
>
>
>
>
>
> > you need to put it in the dict so the view can see it.
>
> > On Feb 27, 2:57 pm, Paul <paul.robinson...@gmail.com> wrote:
>
> > > One last part of the puzzle, this all works ok at the command line
> > > with print row[mysum] but I cannot get the syntax for using this in a
> > > view
>
> > > for example:-
>
> > > controller DAL query:-
> > > def mileage():
> > > mysum = db.t_appointment.miles_to.sum()
> > > +db.t_appointment.miles_from.sum()
> > > groupyyyymm = db.t_appointment.f_start_time.year()|
> > > db.t_appointment.f_start_time.month()
> > > rows = db().select(
> > > mysum,
> > > db.t_appointment.f_start_time.year(),
> > > db.t_appointment.f_start_time.month(),
> > > groupby=groupyyyymm)
>
> > > return dict(rows=rows, sql = db._lastsql)
>
> > > IN VIEW:
> > > {{for row in rows:}}
> > > {{=row[mysum]}}
> > > {{pass}}
>
> > > I get NameError: name 'mysum' is not defined
>
> > > If I try:
>
> > > {{for row in rows:}}
> > > {{=row['mysum']}}
> > > {{pass}}
>
> > > I get:
>
> > > Traceback (most recent call last):
> > > File "D:\web2py-src\web2py\gluon\restricted.py", line 194, in
> > > restricted
> > > exec ccode in environment
> > > File "D:\web2py-src\web2py\applications\caltest\views\info/
> > > mileage.html", line 105, in <module>
> > > File "D:\web2py-src\web2py\gluon\dal.py", line 4331, in __getitem__
> > > return dict.__getitem__(self, key)
> > > KeyError: 'mysum'
>
> > > On Feb 27, 2:08 pm, Anthony <abasta...@gmail.com> wrote:
>
> > > > > That syntax works and I can use that to refer to the data, I could see
> > > > > that a row object had an '_extra' dict for the selected expressions
> > > > > but could not see that the data could be referred to be the name of
> > > > > the expression 'mysum' (its in there somewhere but not sure where!!)
>
> > > > The book example does show that usage:
>
> > > > >>> sum = db.log.severity.sum()
> > > > >>> print db().select(sum).first()[sum]
>
> > > > Actually, when you do row[mysum], the __getitem__ method of the Row
> > > > object
> > > > automatically does row[str(mysum)]. Since mysum is a DAL Expression
> > > > object,
> > > > its __str__ method converts it to the SQL syntax for the particular
> > > > database adapter you are using, which in this case is
> > > > "(SUM(t_appointment.miles_to)
> > > > + SUM(t_appointment.miles_from))". The Row __getitem__ method first
> > > > looks
> > > > for that key in its _extra dict and returns the value if found. So, when
> > > > you do:
>
> > > > row[mysum]
>
> > > > This is essentially what is happening behind the scenes:
>
> > > > 1. row[str(mysum)]
> > > > 2. row['(SUM(t_appointment.miles_to) +
> > > > SUM(t_appointment.miles_from))']
> > > > 3. row['_extra']['(SUM(t_appointment.miles_to) +
> > > > SUM(t_appointment.miles_from))']
>
> > > > Anthony
