CODEWITHSUNDEEP
databasejoinweb2py
mike10010100
mike10010100

Reputation: 163

Web2py Database Joins and Database Design

This may be a stupid question, and I've read over this: http://web2py.com/books/default/chapter/29/06#One-to-many-relation

But I'm still having troubles understanding where I went wrong with my database declaration.

Here's the layout of what I want my tables to look like, with references and everything:

http://imgur.com/fpaDb

Here's the web2py code that I used in db.py

db.define_table('Course',Field('CallNumber','integer'),Field('Section','string',),Field('Title','string'),Field('MinCredit','integer'),Field('MaxCredit','integer'),Field('MaxEnrollment','integer'),Field('CurrentEnrollment','integer'),Field('Status','string'),Field('Instructor1','string'),Field('Term','string'))
db.define_table('Meeting',Field('CallNumber',db.Course),Field('Day','string'),Field('StartTime','string'),Field('EndTime','string'),Field('Site','string'),Field('Building','string'),Field('Room','string'),Field('Activity','string'))
db.define_table('Requirement',Field('CallNumber',db.Course),Field('Control','string'),Field('Argument','string'),Field('Value1','string'),Field('Operator','string'),Field('Value2','string'))

What I'm trying to do is make sure that the Meeting and Requirement tables refer back to the Course table not by the arbitrary id that web2py assigns the entries in the Course table, but by the CallNumber instead.

Any help would be appreciated. Thanks.

Upvotes: 2

Views: 1512

Answers (1)

Anthony
Anthony

Reputation: 25536

A reference field stores the primary key of the referenced table, which is the id field. If you want the CallNumber value of a referenced record, you can easily get it when you do a join:

db.define_table('Course', Field('CallNumber', 'integer'), ...)
db.define_table('Meeting', Field('Course', db.Course), ...)

row = db(db.Course.id == db.Meeting.Course).select().first()
print row.Course.CallNumber

You can also do a recursive select:

row = db(db.Meeting).select().first()
print row.Course.CallNumber

Note, the recursive select is less efficient when looping over multiple records because it does a separate database query for each record. The inner join method above is more efficient in that case, as it involves just a single query to retrieve the entire set of joined records.

Upvotes: 2

Related Questions