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.
