How do you define a column name on summed field (i.e. how to do the equivalent of an sql 'select ..... as colname'

[Paul]

In the dal I'm selecting two summed fields and adding them together,
the column name in the set object ends up being called
'(SUM(t_appointment.miles_to) + SUM(t_appointment.miles_from))', how
would I define the dal query to get an better column name (e.g.
total_miles) ?

Current query is:


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)


Thanks,

Paul

[Anthony]

I'm not sure you can do that. In order to access the mysum column in the result, you would do:

row[mysum]

Anthony

[Alan Etkin]

I know you want to modify the query, but you could replace the string
when the rows are passed to a helper instance.

[Paul]

Thanks Anthony,

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!!)

[Anthony]

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

[Paul]

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

[Anthony]

The view only sees what you explicitly pass to it from the controller via the returned dictionary, so you would need to do:

def mileage():

    [snip]

    return dict(rows=rows, mysum=mysum, sql=db._lastsql) 

Or you could just do:

    return locals()

which returns a dictionary of all the local variables defined in mileage().

Anthony

[Massimo Di Pierro]

you need to put it in the dict so the view can see it.

[Paul]

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:

[Massimo Di Pierro]

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

[Anthony]

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 problem is in the latter case, you have to pass foo to the view -- not a big deal with just foo, but more cumbersome with multiple expressions.

Anthony 

[Massimo Di Pierro]

you can do:

for row in rows: row.foo = row[foo]

and then you do not have to pass it.