did I structure my table and table reference wrong?

[LoveWeb2py]

Hi... I'm trying to create a grading system where I can reference the questions_answered with the questions id.

I want to be able to load all of the questions from the first table and and see whether or not the student got them right in the second. If the student got them right I actually only want the title of the question and not the question itself but I never made a reference to it in my questions_answered. I do however have a "question" field in my questions_answered which references the questions table. Is there a way for me to pull the title as well?

db.define_table('questions',

                Field('id','integer'),

                Field('title','string'),

                Field('question','text'),

                Field('answer','string'),

                migrate=False)

db.define_table('questions_answered',

                Field('id','integer'),

                Field('question', type='reference questions',

                    requires=IS_EMPTY_OR(IS_IN_DB(db,db.questions.id,"%(question)s")),represent=lambda id, r: db.questions(id).question),

                Field('submitted','string', writable=False),

                Field('answered_by',db.auth_user),

                Field('correct','boolean'),

                format='%(name)s', migrate=True)

[Leonel Câmara]

Well if qa is a questions_answered record you can just do qa.question.title (this will do a select to get the question title).

[LoveWeb2py]

You're awesome. Thank you! Web2py is the best!

[Anthony]

Note, qa.question.title will do a recursive select (a separate additional query for each record), so if you have to loop through all questions, this could be made more efficient with a join:

rows = db((db.questions_answered.answered_by == auth.user_id) &
          (db.questions_answered.question == db.questions.id)).select(
           db.questions.ALL, db.questions_answered.correct)

Then in a view, you can do something like:

{{for row in rows:}}
  <div>{{=row.questions.title if row.questions_answered.correct else row.questions.question}}</div>
{{pass}}

Also, you should not create a field with the name "id". In fact, when you do that, it will simply be ignored, as web2py will automatically create an "id" field of type "id", which will be an autoincrementing integer field. You can instead name the "id" type field something other than "id", but you should only do that if working with a legacy database that already has such a field with a different name.

Anthony