Il 28/04/14 22:39, Manuele Pesenti ha scritto:
>> there's no need to introduce a new method.... you can use expression
>> > themselves in the select, and extract the field as usual
>> >
>> > myexpr = 'first_value(b_field) over (partition by a_field order by
>> > b_field)'
>> > rows = db2(db2.test_table.id > 0).select(myexpr, db2.test_table.a_field)
>> > for row in rows:
>> > print row.aa, row[myexpr]
> uh! it sounds good :)
> I'll surely try it
ok, I tried without success...
with the solution I found patching the dal source code adding the
array_agg expression method as described I defined this query.
Can you help me to get the same using expressions?
max_severity = db.r_monitoring.severity.coalesce_zero().max()
db(
(db.r_monitoring.data_manager_id==db.p_data_manager.id) & \
(db.p_data_manager.location_id==db.p_installation.id))._select(
db.p_installation.id,
db.p_installation.name,
db.p_installation.the_geom,
db.p_installation.customer,
db.r_monitoring.status_info.array_agg().with_alias('status_info'), #
<--- to be substituted with an expression
db.p_data_manager.id.array_agg().with_alias('data_manager_id'),
# <--- to be substituted with an expression
max_severity.with_alias('severity'),
groupby = db.p_installation.id,
orderby = ~max_severity
)
here you can find the model that the query referes to... they are three
tables with one to many relationship in cascade
pdb.define_table('p_installation',
location,
Field('customer', 'integer', required=True,
represent = lambda id, row: customer_repr(id, row),
requires=IS_IN_DB(
db(my_customers_query),
db.auth_group.id, label=customer_repr2,
orderby=[db.auth_group.role],
groupby=None)
),
auth.signature
)
p_data_manager = pdb.Table(pdb, 'p_data_manager',
Field('location_id', 'reference p_installation',
label=T("Installation")),
connection,
)
pdb.define_table('p_data_manager',
p_data_manager,
Field('dsn', readable=False, compute=lambda row:
getdsn(**row.as_dict())),
auth.signature,
#Field.Method('remote_dsn', lambda row:
getdsn(**row.as_dict()['p_data_manager'])),
common_filter = lambda query: pdb.p_data_manager.is_active==True,
)
pdb.define_table('r_monitoring',
Field('data_manager_id', 'reference p_data_manager'),
Field('status_info', 'json'),
Field('severity', 'integer', compute = lambda row:
getSeverity(row.status_info['status'])),
auth.signature.created_on,
auth.signature.modified_on
)
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
