3-table many-to-many with IS_IN_DB

[sunny]

Hi,

for days I try to find a solution for my problem which I'm sure
exists.
I want to create/update the many2many relationship
"auth_user -> auth_membership -> auth_group"
in an auth_user form with an "IS_IN_DB multiple=true"-widget.

How can I do that?

Thanks in advance,
Sven

[howesc]

i do this for admins of one of my sites:  (note that i have customizations to my auth_user table so this is not a drop-in method for your site).

@auth.requires_membership('admin')
def auth_user():
    users_form=None
    retval = dict(aahome=A("Forms Home", _href=URL(r=request, f='index')))
    role_values = []
    #remove bio requirements from auth pane
    db.auth_user.bio.requires=None

    table = db.auth_user

    if request.args and request.args[0]:
        selected_membership = db(db.auth_membership.user_id == request.args[0]).select(db.auth_membership.group_id)
        #build the current roles list, before the update
        query = None
        for r in selected_membership:
            if query == None:
                query = db(db.auth_group.id == r.group_id).select(orderby=db.auth_group.role)
            else:
                query = query | db(db.auth_group.id == r.group_id).select(orderby=db.auth_group.role)

        #@TODO: remove user specific roles from query
        roles = query

        retval['all roles for %s' % request.args[0]] = SQLTABLE(roles,linkto,orderby=True,_class='sortable')
        for role in roles:
            role_values.append(role.id)

        #check the form submit
        users_form = SQLFORM(table, request.args[0])
        if users_form.accepts(request.vars, session, formname='userupdate'):
            for role in request.vars.roles:
                # currently trusts that only valid role id's will be submitted
                if not auth.has_membership(role, users_form.vars.id):
                    auth.add_membership(role, users_form.vars.id)
            for role in roles:
                #delete roles that have been removed
                if str(role.id) not in request.vars.roles:
                    auth.del_membership(role.id,
                                        users_form.vars.id)

            redirect(URL(r=request,f='auth_user', args=request.args))


        #add roles multi-select to the form
        rows = db(db.auth_group.id > 0).select(db.auth_group.id, db.auth_group.role, orderby=db.auth_group.role)

        import logging
        logging.info(rows)
        logging.info(role_values)
        opts = [OPTION(r['role'], _value=r['id']) for r in rows]

        users_form.components[0].insert(-1,
                              TR(TD('Roles:'),
                                 TD(SELECT(opts, _name="roles",
                                           #@TODO: add better requires if we can
                                           # make it work with lists
                                           requires=IS_NOT_EMPTY(),
                                           _multiple=True,
                                           value=role_values))))

    else:
        users_form = SQLFORM(table)
        if users_form.accepts(request.vars, session, formname='userupdate'):
            #create role
            auth.add_membership(auth.add_group('user_%d' % users_form.vars.id,'user_%d' % users_form.vars.id), users_form.vars.id) ## a group for this user


    #get list of users to display
    orderby=request.vars.orderby
    if orderby == None:
        orderby=db.auth_user.id
    rows = db( db.auth_user.id > 0
              ).select(db.auth_user.id,
                       db.auth_user.first_name,
                       db.auth_user.last_name,
                       db.auth_user.email,
                       db.auth_user.registration_key)

    headers = {'auth_user.id':T("ID"),
               'auth_user.first_name':T("First Name"),
               'auth_user.last_name':T("Last Name"),
               'auth_user.email':T("Email"),
               'auth_user.registration_key':T("Registration Key")}
    tbl = SQLTABLE(rows,linkto,orderby=True,_class='sortable', truncate=128,
                   headers=headers)

    retval['aform'] = users_form
    retval['users'] = tbl

    return retval

[Ovidio Marinho]

You mean that this routine makes your administrators register users so their instances. How this works can explain?
      

       Ovidio Marinho Falcao Neto

                Web Developer
             ovid...@gmail.com 

          ovidio...@itjp.net.br

                 ITJP - itjp.net.br
               83   8826 9088 - Oi

               83   9334 0266 - Claro
                        Brasil

              

2012/1/17 howesc <how...@umich.edu>

[Cliff Kachinske]

in auth_user I have the request_tenant field, as we have already discussed.  It is set like this:

if 'auth' in session:

  db.auth_user.request_tenant.default = session.auth.user.request_tenant

else:
  db.auth_user.request_tenant.default = ''

Each tenant has a different domain of the form tenant.example.com.

I create an auth_user record for the tenant's administrator and make his request_tenant field the same as his domain.

Then when the administrator creates other users, the request tenant default makes the new user's request_tenant same as the administrator's.

I have two other tables something like this.

db.define_table('tenant', Field('name', ....), Field('domain', ...), ...)

db.define_table('tenant_group', Field('tenant', db.tenant,), Field('group_id'), Field('is_default', 'boolean', default=False)...)

db.tenant_group.tenant.requires = IS_IN_DB(db, 'tenant.domain', '%(name)s')

I need the tenant group table because some tenants have different access rights.

The is_default field identifies which access rights for all the users related to the tenant.

Then the controller is something like this:

def add():

  form = SQLFORM(db.auth_user, ...)

  if form.process().accepted:

    query = ((db.tenant_group.tenant==session.auth.user.request_tenant) &

                   (db.tenant_group.is_default==True))

    rows = db(query).select(db.tenant_group.group_id)

    for r in rows:

    # this would be faster with a bulk insert!

      db.auth_membership[0] = dict(user_id=form.vars.id, group_id=r.group_id)

    session.flash = blah

    redirect(URL('edit')) # This is so the admin can further refine the user's access privileges.