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 idb(
(row.auth_user.id == db.auth_membership.user_id)
& (db.auth_group.id == db.auth_membership.group_id)
& (db.auth_group.role == 'operador')
).isempty()
idb.auth_user.is_operador = Field.Virtual('is_operador', is_operador)
db = idb
grid = SQLFORM.grid(
db.auth_user,
details=False,
create=False
)
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.
