Thanks.
quarta-feira, 3 de Abril de 2019 às 17:12:44 UTC+1, tim....@conted.ox.ac.uk
escreveu:
>
> Apologies. Left a lot of cruft in there which should be edited out now.
>
> On Wednesday, 3 April 2019 17:10:49 UTC+1, tim....@conted.ox.ac.uk 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,
>>> tim....@conted.ox.ac.uk 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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
