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.
