Insert multiple data using SQLFROM.grid with selectable signature in another table

Question

Good day to all stackoverflow web2py Guru's here....

I found this link and its 2 years old now. I have a problem and I don't know how to code it.

I have 2 Entities(Tables) and it's a M:M relationship

First table: The Instructors (Advisers) Second table: The Lists of Students

now Advisers handled many students and students have many advisers right?

so I create a third table and I named it to Student_Adviser

School Year

db.define_table('school_year',
            Field('sy',),
            Field('current_year', 'boolean'))

List of Students

db.define_table('student_list',
            Field('lastname'),
            Field('firstname'))

these are the fields in Student_Adviser

db.define_table('stud_adviser',
            Field('sy_id', 'reference school_year', label='School Year'),
            Field('adv_id', 'reference auth_user', label='Adviser'),
            Field('stud_id', 'reference student_list', label='Student', unique=True)
           )

in the controller

def getStudent():
    form = SQLFORM.grid(db.Student_List, csv=False, create=False, selectable=(need code here))
    return locals()

I ask this kind of question because it help the Advisers to get the list of students by activating multiple check boxes so after he/she choose the students he/she will click the submit button and viola all the checked data will automatically add to the Student_Adviser table. Is my idea is possible in web2py?

ADDITIONAL 1:

Please also add a code that will automatic insert also the auth_user.id who is logged-in.

Let's say when Instructor 1 auth_user.id is 1

Instructor 1 is logged-in so all the transaction he/she will do the default value of adv_id in Student_Adviser table will always be 1 and so on.

ADDITIONAL 2:

I'm getting an error when I tried to manual add the data in the Student_Adviser table using the db interface.

btw how will I post a Traceback error? the Code Sample won't work in Traceback. I can't post the error because it will destroy the format... but this is the last line of error code (please based the table in Student_Adviser).

IntegrityError: foreign key constraint failed

Answer 1

Something like this should do it:

@auth.requires_login()
def getStudent():
    db.stud_adviser.sy_id.default = db.school_year(current_year=True).id
    db.stud_adviser.adv_id.default = auth.user_id
    def add_students(ids):
        for id in ids:
            db.stud_adviser.insert(stud_id=id)
    form = SQLFORM.grid(db.student_list, create=False, selectable=add_students,
                        csv=False)
    return dict(form=form)

The selectable argument is a callback function, which receives the list of record IDs selected in the grid. The add_students function supplied as that argument loops through the IDs and inserts a new record in the stud_adviser table for each one. Because the school year and adviser IDs should be the same for each record, they are set by setting the default attributes of their respective fields (for the school year, I assume you want the ID of the current school year) -- by excluding those fields from the .insert() call, the default values will be inserted automatically.

Note, to display more useful details in the grid (rather than the school year, adviser, and student record IDs), you can define the "format" attribute on each of the tables:

db.define_table('school_year',
     Field('sy'),
     Field('current_year', 'boolean'),
     format='%(sy)s')

db.define_table('student_list',
     Field('lastname'),
     Field('firstname'),
     format='%(lastname)s')

Because the db.auth_user table is defined automatically (with a default "format" attribute), you must change its "format" attribute after the fact -- so, somewhere after calling auth.define_tables():

db.auth_user._format = '%(last_name)s'

With the "format" attributes defined as above, now any reference field that references these tables will get a default "represent" attribute based on the "format" attribute of the referenced table. This will control the display of the reference field values in the grid (as well as in SQLFORMs and SQLTABLEs).