Help with project (many to many form or table)

[Juan Gutiérrez]

Hello, I'm creating a small project to test the use of web2py.

After going through some of the documentation for web2py I've found myself in quite a problem to deal with a many to many insert/update form.

The simplified context for the example is this:

Model: 

There are people (person) and each can only have a home (home). Homes (home) have many types of rooms (home_room). Types of rooms (room) are asigned to homes (home). A person can spend an amount of minutes in a specific room of his home (person_room).

The table to record the amount of minutes a person spends in a room can have only one record for a person/room of the home.

Controller:

Index. Show a list of the people in the DB.

Register minutes. Shows a list of the rooms available to the person according to the home and process the request to update amount of minutes for each room.

View:

Index. Displays a list of records for person. Each person links to the register url and using the args sends the person's id.

Register minutes. Should display a table (or form) with the rooms and any previous minutes registered to a person so the amounts can be entered or updated.

So my problem is creating a table (or form) that presents the left join for home_rooms and person so I can enter the minutes for each room and submit the information. I've been thinking a solution could be to insert first any records from the left join into the person_room table before creating the table (or form), but this still leaves me the problem of showing several records to update the amount of minutes.

CODE:

Model:

db.define_table('room', Field('name', 'string'))
db.define_table('home', Field('name', 'string'))
db.define_table('home_room', Field('description', 'string'), Field('home_id', 'references home'), Field('room_id', 'references room'))
db.define_table('person', Field('name', 'string'), Field('home_id','references home'))
db.define_table('person_room', Field('minutes', 'integer'), Field('person_id', 'references person'), Field('home_room_id', 'references home_room'))

Data:

Home:

home.id home.name

1 city

2 beach

3 winter

Room:

room.id room.name

1 principal

2 secundary

3 living

4 dining

5 gaming

Home_Room:

home_room.id home_room.description home_room.home_id home_room.room_id

1 city principal 1 1

2 city secondary 1 2

3 city living 1 3

4 beach principal 2 1

5 beach living 2 3

6 beach dining 2 4

7 winter principal 3 1

8 winter living 3 3

9 winter gaming 3 5

Person:

person.id person.name person.home_id

1 David 1

2 Sara 2

3 Charles 3

4 Rose 1

5 Fred 2

6 Martin 3

Controllers (default):

def index():
    ppl = db().select(db.person.id, db.person.name)
    return dict(people=ppl)


def register():
    aForm = None
    aPerson = db(db.person.id==request.args(0,
                                                     cast=int,
                                                     otherwise=URL('index')
                                                    )
                     ).select(db.person.id,
                              db.person.name,
                             ).first() or redirect(URL('index'))
    fields = ['person_id', 'home_room_id','minutes']
    aForm = SQLFORM(db.person_room, fields=fields)
    aForm.vars.person_id = aPerson.id
    aForm.process(keepvalues=True)
    return dict(person=aPerson, form = aForm)

Views:

default/index

{{extend 'layout.html'}}


<table>
{{if len(people) == 0:}}
        <tr><td>Theres no information</td></tr>
{{else:}}
        {{for row in people:}}
        <tr>
            <td>{{=A('%s'%row.name, _href=URL("register", args=row.id))}}</td>
        </tr>
        {{pass}}
{{pass}}
</table>

default/register

{{extend 'layout.html'}}
<table>
 <tr>{{=person.name}}</tr>
 <tr>{{=form or ''}}</tr>
</table>