Querying auth_user by group

[ahz...@gmail.com]

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.

[Richard Vézina]

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

--
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+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[ahz...@gmail.com]

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

[Richard Vézina]

Add a where clause to remove them something like this :

~db.auth_group.startswith('user_')

[Richard Vézina]

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