Add a where clause to remove them something like this :
~db.auth_group.startswith('user_')
On Thu, Jul 7, 2016 at 2:57 PM, <[email protected]> wrote:
> Richard,
>
> Thank you for your reply.
>
> Using the code you suggested, implemented as this:
>
> users=db().select(
> db.auth_user.id, db.auth_user.first_name, db.auth_user.last_name, db.
> auth_group.role,
> left=[db.auth_membership.on(db.auth_user.id == db.auth_membership.
> user_id),
> db.auth_group.on(db.auth_membership.group_id==db.auth_group.id)])
>
> returns all groups, including the user_%d groups. It's harder to read, so
> I would like to see something like "None" where the user belongs only to a
> user_%d group.
>
> My other solution right now, rather than a report like this, is to check
> group membership in the index() controller.
>
>
> Andrew
>
>
> On Thursday, July 7, 2016 at 12:29:27 PM UTC-6, Richard wrote:
>>
>> Why not do that instead of you first example :
>>
>> db(db.auth_user.id > 0).select(..., left=[db.auth_membership.on(
>> db.auth_user.id == db.auth_membership.user_id),
>> db.auth_group.on(db.auth_membership.group_id==db.auth_group.id)])
>>
>> Richard
>>
>> On Thu, Jul 7, 2016 at 1:30 PM, <[email protected]> wrote:
>>
>>> I assign each user either group A or B, and by default web2py assign
>>> users to the user_%d groups. I would like to query which users are in
>>> groups A, B, and NULL so that (1) I can verify each user is in the right
>>> group and (2) no users are in zero groups.
>>>
>>> I have this controller
>>>
>>> users = db(~db.auth_group.role.like('user%')).select(db.auth_user.id, db
>>> .auth_user.first_name, db.auth_user.last_name, db.auth_group.role,
>>> left=(db.auth_membership.on(db.auth_user.id==db.auth_membership.
>>> user_id), db.auth_group.on(db.auth_membership.group_id==db.auth_group.id
>>> )),
>>> orderby=db.auth_user.id|db.auth_group.id)
>>> response.flash=db._lastsql
>>> return dict(users=users)
>>>
>>>
>>> Which generates this SQL
>>>
>>> SELECT auth_user.id
>>> ,auth_user.first_name
>>> ,auth_user.last_name
>>> ,auth_group.ROLE
>>> FROM auth_user
>>> LEFT JOIN auth_membership ON (auth_user.id = auth_membership.user_id)
>>> LEFT JOIN auth_group ON (auth_membership.group_id = auth_group.id)
>>> WHERE (NOT (auth_group.ROLE LIKE 'user%' ESCAPE '\'))
>>> ORDER BY auth_user.id
>>> ,auth_group.id;
>>>
>>> However, users with no groups are omitted. I could use something more
>>> like this:
>>>
>>> SELECT auth_user.id
>>> ,auth_user.first_name
>>> ,auth_user.last_name
>>> ,auth_group.ROLE
>>> FROM auth_user
>>> LEFT JOIN auth_membership ON (auth_user.id = auth_membership.user_id)
>>> LEFT JOIN auth_group ON auth_membership.group_id = auth_group.id and
>>> NOT auth_group.ROLE LIKE 'user%'
>>>
>>> ORDER BY auth_user.id
>>> ,auth_group.id;
>>>
>>>
>>>
>>>
>>> I could use executesql(), but I am trying to not give up too easily on
>>> the DAL. Also, using DAL it's easy to use SQLTABLE in the view.
>>>
>>> --
>>> 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.
>>>
>>
>> --
> 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.
>
--
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.
