web2py, Database relationships and permissions

Question

So i've this problem i've 2 tables for example templates(id,user_id,template_name,reference) user_settings(id,user_id,default_template)

so each user can create many templates and in his settings he can choose a default template that he will always use

so now there is many users so when a user want to choose a default template, he can see all templates (his own templates and the templates for the other users)

tables are so defined:

db.define_table('i2l_templates',
    Field('id','id',
          represent=lambda id:SPAN(A('view',_href=URL('view_template',args=id)),' | ',
                                          A('edit',_href=URL('edit_template',args=id)))),
    Field('user_id', db.auth_user, default=auth.user_id, writable=False,readable=False,
          label=T('User Id')),
    Field('template_name', requires=IS_NOT_EMPTY(), type='string',
          label=T('Template name')),
...
...
...
)

---

db.define_table('user_settings',
    Field('id','id',
          represent=lambda id:SPAN(A('view',_href=URL('view_settings',args=id)))),
    Field('user_id', db.auth_user, default=auth.user_id, writable=False,readable=False,
          label=T('User Id')), 
    Field('standard_template_id', templates,
          label=T('Standard Template')),
...
...
)

what should i do to make user choose only his own template!

Answer 1

First, shouldn't

    Field('standard_template_id', templates,

be

    Field('standard_template_id', db.i2l_templates,

For a reference field, the default form validator is IS_IN_DB(db,'<table>.id'), which will select all records in the referenced table. However, you can override the default validator and specify a subset of records:

requires = IS_IN_DB(db(db.i2l_templates.id==auth.user_id),
                    'i2l_templates.id', '%(template_name)s')

See here for more on database validators.