Notice : db().select(...) empty set will be abandonned (deprecated) probably... So better to specify table you are querying as you are writting new code...
Ref.: https://github.com/web2py/pydal/issues/388 Richard On Thu, Jul 7, 2016 at 3:03 PM, Richard Vézina <[email protected]> wrote: > 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.
