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
