how to set a multi-column unique in web2py

Question

In order to make many to many relation ship I make a middle table to combine two tables ,the tables like this:

db.define_table('problem',
                Field('task_id','reference task'),
                Field('title','string',unique=True,length=255))
db.define_table('task',
                Field('title','string',unique=True,length=255),
                Field('course_id','reference courses'))

db.define_table('belong',
                Field('task_id','reference task'),
                Field('problem_id','reference problem')
                )
db.belong.task_id.requires=IS_IN_DB(db,'task.id','%(title)s')
db.belong.problem_id.requires=IS_IN_DB(db,'problem.id','%(title)s')

I use SQLFORM to insert into belong table.I want there are no duplicate task and problem. suppose there is a record like (1,task1,problem1)already exist in belong table,then if I choose task1 in SQLFORM,how to make the problem_id dropdown list not to show problem1. I have found similar question here and do some test like the following in db.py:

test1:  

db.belong.problem_id.requires=IS_NOT_IN_DB(db(db.belong.task_id==request.vars.task_id),'belong.problem_id').

test2:

db.belong.problem_id.requires=IS_NOT_IN_DB(db(db.belong.task_id==request.vars.task_id),'problem.id','%(title)s').

test3:

def my_form_processing(form):
    a = form.vars.task_id
    b=form.vars.problem_id
    query=(db.belong.task_id==a)&(db.belong.problem_id==b)
    if query :
        form.errors.a= 'the record has existed'

def assignproblem():
    form=SQLFORM(db.belong)
    if form.process(onvalidation=my_form_processing).accepted:
        response.flash='form accepted'
    elif form.errors:
        response.flash='form has errors'
    else:
        response.flash='please fill out the form'
    return dict(form=form)

but it still not been solved.

Answer 1

If you want the choices in the problem_id dropdown to change dynamically based on the task_id selection, then you will need to use JavaScript (and likely make an Ajax request to populate the dropdown). For possible solutions, see this answer.

As for your tests above, test1 should do the proper validation, but only after the form has been submitted (i.e., the form will allow any combination of task_id and problem_id to be selected but will then report an error if a duplicate combination has been submitted).

test3 does not work as is because the query object just specifies the database query without actually doing a select from the database. Instead, you must call the .select() method, or more simply, the .count() method:

if db((db.belong.task_id == a) & (db.belong.problem_id == b)).count():
    form.errors.a= 'the record has existed'