Apologies. Left a lot of cruft in there which should be edited out now. On Wednesday, 3 April 2019 17:10:49 UTC+1, [email protected] wrote: > > That will be different. SQLFORM.grid doesn't support aggregation, so > you'll need to use virtual fields to do the work, which is what I do to > show invoices along with their current calculated balance. It's not > sortable, though. > > Something like: > > def is_operador(row): > return not db( > (row.auth_user.id == db.auth_membership.user_id) > & (db.auth_group.id == db.auth_membership.group_id) > & (db.auth_group.role == 'operador') > ).isempty() > > db.auth_user.is_operador = Field.Virtual('is_operador', is_operador) > > grid = SQLFORM.grid( > db.auth_user > ) > > You'll need a virtual field for each row, and it's running subqueries so > it's not as light on the DB, but it works without needing a hand-rolled > table. > > > On Wednesday, 3 April 2019 16:37:16 UTC+1, João Matos wrote: >> >> Thanks. That worked. I just corrected the groupby you sent. >> >> Now I have another problem. Unfortunately a SQLFORM.grid doesn't accept a >> SELECT. >> The only solution I have is to create a view in SQLite, which defies the >> purpose of moving from the SQLite SELECT to DAL. >> >> Any ideas on how I can solve this? >> >> Thanks, >> >> JM >> >> quarta-feira, 3 de Abril de 2019 às 16:24:24 UTC+1, >> [email protected] escreveu: >>> >>> I think that implicit conversion of "auth_group.role = 'Operador'" to a >>> 1 is a Sqlite-specific thing. What you want to use here is case: >>> >>> query = db().select( >>> db.auth_user.id, >>> db.auth_user.username, >>> db.auth_user.canceled_on, >>> db.auth_user.canceled_by, >>> (db.auth_group.role == 'Operador').case(1, 0).sum().with_alias(' >>> operador'), >>> (db.auth_group.role == 'Verificador').case(1, 0).sum().with_alias(' >>> verificador'), >>> (db.auth_group.role == 'Supervisor').case(1, 0).sum().with_alias(' >>> Supervisor'), >>> >>> left=( >>> db.auth_membership.on(db.auth_user.id == db.auth_membership. >>> user_id), >>> db.auth_group.on(db.auth_group.id == db.auth_membership.group_id >>> ), >>> ), >>> groupby=[db.auth_user.username, db.auth_user.id, db.auth_user. >>> canceled_on, db.auth_user.canceled_by], >>> orderby=db.auth_user.username, >>> ) >>> >>> On Monday, 1 April 2019 23:41:51 UTC+1, João Matos wrote: >>>> >>>> Hello, >>>> >>>> I have this SELECT in SQLite >>>> >>>> result = db.executesql( >>>> "SELECT auth_user.id, auth_user.username, " >>>> "auth_user.canceled_on, " >>>> "auth_user.canceled_by, " >>>> "SUM(auth_group.role = 'Operador') operador, " >>>> "SUM(auth_group.role = 'Verificador') verificador, " >>>> "SUM(auth_group.role = 'Supervisor') supervisor " >>>> "FROM auth_user " >>>> "LEFT JOIN auth_membership ON auth_user.id = >>>> auth_membership.user_id " >>>> "LEFT JOIN auth_group ON auth_group.id = >>>> auth_membership.group_id " >>>> "GROUP BY username ORDER BY username" >>>> ) >>>> >>>> and would like to convert it to web2py's DAL. >>>> I made this >>>> >>>> query = db().select( >>>> db.auth_user.id, >>>> db.auth_user.username, >>>> db.auth_user.canceled_on, >>>> db.auth_user.canceled_by, >>>> left=( >>>> db.auth_membership.on(db.auth_user.id == db.auth_membership. >>>> user_id), >>>> db.auth_group.on(db.auth_group.id == db.auth_membership. >>>> group_id), >>>> ), >>>> groupby=db.auth_user.username, >>>> orderby=db.auth_user.username, >>>> ) >>>> >>>> but I can't find any way to integrate the sums. >>>> >>>> Does anyone know how to do it? >>>> >>>> Thanks, >>>> >>>> JM >>>> >>>
-- 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.
