How can I join 3 tables and output all three toget

问题:

model:

# coding: utf8
db.define_table('dept',
                Field('name',unique=True,label='Department Name'),
                format='%(name)s')

db.define_table('course',
                Field('dept_id','reference dept'),
                Field('name',unique=True,label='Course Name'),
            format='%(name)s')

db.define_table('files',
                Field('course_id', 'reference course'),
                Field('documentx','upload'))

controller:

def show_doc():
    rows = db( db.course.id == db.files.course_id , db.dept.id==db.course.dept_id).select()
    return rows

What I am trying to do is to join the department table "dept" with the "course" table and the "course" table with the "files" table. So when outputting it shows a table with department with course and files all together. The solution doesn't work. It only creates a join between the "course" table and the "files" table.

回答1:

As noted in the book, it should be:

db((db.course.id == db.files.course_id) & (db.dept.id==db.course.dept_id))

回答2:

In your controller, you can simply select all your files :

def show_doc():
    rows = db(db.files.documentx).select()
    return dict(rows=rows)

Then, in your view "show_doc.html" you can access to the linked fields :

{{extend 'layout.html'}}
{{for row in rows:}}
    <div>Department : {{=row.course_id.dept_id.name}} Course : {{=row.course_id.name}}</div>
    {{if row.documentx:}}
        <div><a href={{=URL('default', 'download', args=row.documentx)}}>Download file</a></div>
    {{pass}}
    <hr>
{{pass}}