join and sum - display in view

[andrej burja]

in controler i do join and sum
the result si rows object
in response._vars the sum is displayed in
SUM(t_table.f_field)

what is the syntax to display that in view?

andrej

[Anthony]

You can do

sum(r.t_table.f_field for r in rows)

[Anthony]

Wait, do you mean that you did the sum as part of the select? What exactly did you return to the view (the whole Rows object), and how do you want to display the results? In general, you would access the sum "field" like rows[row_number]['SUM(t_table.f_field)']. Note, you can also assign the sum to a variable prior to the select and then use the variable name to access the sum from the Rows object -- see http://web2py.com/book/default/chapter/06#belongs (sum is discussed right after belongs).

On Thursday, October 20, 2011 1:27:02 AM UTC-4, andrej burja wrote:

[Martín Mulone]

FROM THE BOOK

Previously, you have used the count operator to count records. Similarly, you can use the sum operator to add (sum) the values of a specific field from a group of records. As in the case of count, the result of a sum is retrieved via the store object:

1. 2. 3.

>>> sum = db.log.severity.sum() >>> print db().select(sum).first()[sum] 6

Expression can be combined to form more complex expressions. For example here we are computing the sum of the length of all the servery strings in the logs, increased of one:

1. 2.

>>> sum = (db.log.severity.len()+1).sum() >>> print db().select(sum).first()[sum]

2011/10/20 Anthony <abas...@gmail.com>

--

 http://martin.tecnodoc.com.ar

[andrej burja]

controller:

def person():
    sum = db.t_payement.f_value.sum()
    rows= db().select(db.t_person.f_name,db.t_person.f_surname,db.t_person.f_city, sum, groupby=db.t_person.id,
    left=db.t_payement.on(db.t_person.id==db.t_payement.f_person))
    return dict(rows=rows)

view:

{{=TR(TD(row.t_person.f_name,' ',row.t_person.f_surname),TD(????))}}

i want to display sum of payments for each person
in headers of table ({{=BEAUTIFY(response._vars)}}) in default view this is shown as
SUM(t_payement.f_value)

[andrej burja]

it si probably something starting with
row._extra
because rows object starts with
<Row {'_extra': <Row {'SUM(t_payement.f_value)': 160.0}>, 't_person':

[Anthony]

Try

TD(row[sum])

or

TD(row['SUM(t_payement.f_value)'])

Anthony

[Anthony]

On Thursday, October 20, 2011 8:43:10 AM UTC-4, Anthony wrote:

Try

TD(row[sum])

Actually, the above will only work if the controller returns 'sum' in the dictionary -- otherwise it will be undefined in the view.

[andrej burja]

this one works

TD(row['SUM(t_payement.f_value)'])

and also this

TD(row._extra['SUM(t_payement.f_value)'])

[Massimo Di Pierro]

Please use Anthony's advice:

row[sum]

Do not use

_extra

because it is deprecated and do not use

row['SUM(t_payement.f_value)']

because the syntax may not work with every database engine.

[andrej burja]

with
row[sum]
it doesn't work
<type 'exceptions.KeyError'>('<built-in function sum>')

"Actually, the above will only work if the controller returns 'sum' in the dictionary -- otherwise it will be undefined in the view."

what shoul i change in the controller?

[Martín Mulone]

I think is row['sum'] 

2011/10/21 andrej burja <andrej...@gmail.com>

--

 http://martin.tecnodoc.com.ar

[Anthony]

You define 'sum' in the controller, so you have to pass it to the view in order to reference it in the view. So, in the controller, instead of:

return dict(rows=rows)

do

return dict(sum=sum, rows=rows)

Actually, because 'sum' is a Python builtin, you might be better off naming it something else.

Anthony 

[Anthony]

On Friday, October 21, 2011 6:03:53 AM UTC-4, Martin.Mulone wrote:

I think is row['sum']

The actual dict key is 'SUM(t_payement.f_value)', so 'sum' (in quotes) won't work in this case. It turns out that str(sum) equals 'SUM(t_payement.f_value)', though, so you can do row[str(sum)]. However, the row object automatically applies str() to any keys, so row[sum] is equivalent to row[str(sum)]. row is also a callable object, so you can do row(sum).

Note, 'SUM(t_payement.f_value)' is the actual SQL expression generated by the DAL Expression (i.e., db.t_payement.f_value.sum() generates the SQL 'SUM(t_payement.f_value)', at least in the particular DB adapter being used). str(expression) returns this SQL, and it is also used as the key to access the result in the Row object.

Anthony

[andrej burja]

working example

controller:
def person():
    sum = db.t_payement.f_value.sum()
    rows= db().select(db.t_person.f_name,db.t_person.f_surname,db.t_person.f_city, sum, groupby=db.t_person.id, left=db.t_payement.on(db.t_person.id==db.t_payement.f_person))

    return dict(rows=rows, sum=sum)

view:
{{for row in rows:}}
{{=TR(TD(row.t_person.f_name),TD(row[sum]))}}
{{pass}}