The output from the SQLite statement is this
id username canceled_on canceled_by operador verificador
supervisor
4 leitor 0 0 0
3 operador 1 0 0
2 supervisor 0 0 1
1 teclalivre 0 0 1
5 verificador 0 1 0
The last 3 columns are the names of fixed group roles (translated are
operator, checker and supervisor).
I have the SQLite statement working using a view (tip from ValK on another
topic) and a web2py grid but I would like to not be dependent on SQLite and
use the DAL.
I don't understand how I can add the SUM() () to the DAL.
Taking for example one of the SUM lines
SUM(auth_group.role = 'Operador') operador
How can I name the sum output column operador? I can't use the usual
solution of using the headers parameter.
On the other hand, the example of the book doesn't apply to my use case,
because I want the sum to depend on the user but I don't want to have to
execute and store
sum_operator = db(db.auth_group.role == 'Operador').sum()
sum_checker = db(db.auth_group.role == 'Verificador').sum()
sum_supervisor = db(db.auth_group.role == 'Supervisor').sum()
for each user before calling this SELECT
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,
)
Any ideas?
terça-feira, 2 de Abril de 2019 às 10:41:31 UTC+1, Dave S escreveu:
>
>
>
> On Tuesday, April 2, 2019 at 12:45:04 AM UTC-7, João Matos wrote:
>>
>> I had already read it. Unfortunately it didn't help.
>>
>>
> Can you show what you tried with .sum() ? Did you get errors, or did
> you get a result that was unexpected? What was the expected result?
>
> /dps
>
>
>> terça-feira, 2 de Abril de 2019 às 00:23:14 UTC+1, Scott Hunter escreveu:
>>>
>>> This part of the web2py book might help:
>>> http://www.web2py.com/book/default/chapter/06#sum-avg-min-max-and-len
>>>
>>> On Monday, April 1, 2019 at 6:41:51 PM UTC-4, 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.
