On Tuesday, March 27, 2012 6:25:16 PM UTC-4, go94025 wrote:
>
> Hi - new to Web2py and the group. I'm building an app that will need to
> perform Sum and Max against large sets of data. As I'm getting familiar
> with the DAL, I've tried the following (and other combinations) rather than
> fall back to db.executesql.
>
> I'm getting the correct result but can't figure out a way to get only the
> result number (68) without the (SUM(rates.val02). Probably missing
> something basic but I've tried a lot of combinations and can't seem to get
> there.
>
> Thanks in advance.
>
> Model-
> db.define_table('rates',
> Field('rate_name', 'string'),
> Field('val01', 'integer'),
> Field('val02', 'integer'),
>
> Controller-
> def sum1():
> query = db.rates.val02.sum()
> return dict(sum1 = db().select(query))
>
> View-
> {{=sum1}}
>
> Result-
> SUM(rates.val02)68
>
The key for the sum is the serialized SQL generated by the DAL, which is
"SUM(rates.val02)" (note, technically it is adapter-specific). However, you
can use the original object as the key, and it will be serialized to the
actual key for you. So, just do:
return dict(sum1 = db().select(query).first(), query=query)
and in the view:
{{=sum1[query]}}
That is equivalent to:
{{=sum1[str(query)]}}
which is equivalent to:
{{=sum1['SUM(rates.val02)']}}
Note, I changed sum1 to call the .first() method, which extracts the first
(and only) row from the Rows object.
Anthony
