Forum OpenACS Q&A: Response to There's a problem with education/class/one.tcl
Posted by
Dan Wickstrom
on 06/27/00 02:07 PM
Here is the corrected query:
Here is the corrected query (sorry about the long post):
# set sql "select edu_assignments.assignment_name, # edu_assignments.assignment_id, # edu_assignments.due_date, # pset.version_id, # pset.file_extension, # pset.url, # sol.url as sol_url, # sol.file_extension as sol_file_extension, # sol.version_id as sol_version_id, # answers.url as ans_url, # answers.file_extension as ans_file_extension, # answers.file_title as ans_filename, # answers.version_id as ans_version_id, # edu_assignments.electronic_submission_p # from edu_assignments, # edu_assignments edu_assignments1, # fs_versions_latest pset, # solution_files sol, # student_answer_files answers # where edu_assignments.class_id = $class_id # and answers.student_id = $user_id # and user_has_row_permission_p($user_id, 'read', pset.version_id, 'FS_VERSIONS') = 't' # and edu_assignments1.assignment_id = edu_assignments.assignment_id # and edu_assignments.file_id = pset.file_id # and edu_assignments1.assignment_id = answers.task_id # and edu_assignments.assignment_id = sol.task_id # union # select edu_assignments.assignment_name, # edu_assignments.assignment_id, # edu_assignments.due_date, # pset.version_id, # pset.file_extension, # pset.url, # '' as sol_url, # '' as sol_file_extension, # '' as sol_version_id, # answers.url as ans_url, # answers.file_extension as ans_file_extension, # answers.file_title as ans_filename, # answers.version_id as ans_version_id, # edu_assignments.electronic_submission_p # from edu_assignments, # edu_assignments edu_assignments1, # fs_versions_latest pset, # student_answer_files answers # where edu_assignments.class_id = $class_id # and answers.student_id = $user_id # and user_has_row_permission_p($user_id, 'read', pset.version_id, 'FS_VERSIONS') = 't' # and edu_assignments1.assignment_id = edu_assignments.assignment_id # and edu_assignments.file_id = pset.file_id # and edu_assignments1.assignment_id = answers.task_id # and not exists (select 1 from solution_files # where task_id = edu_assignments.assignment_id) # union # select edu_assignments.assignment_name, # edu_assignments.assignment_id, # edu_assignments.due_date, # pset.version_id, # pset.file_extension, # pset.url, # sol.url as sol_url, # sol.file_extension as sol_file_extension, # sol.version_id as sol_version_id, # '' as ans_url, # '' as ans_file_extension, # '' as ans_filename, # '' as ans_version_id, # edu_assignments.electronic_submission_p # from edu_assignments, # edu_assignments edu_assignments1, # fs_versions_latest pset, # solution_files sol # where edu_assignments.class_id = $class_id # and user_has_row_permission_p($user_id, 'read', pset.version_id, 'FS_VERSIONS') = 't' # and edu_assignments1.assignment_id = edu_assignments.assignment_id # and edu_assignments.file_id = pset.file_id # and not exists (select 1 from student_answers # where task_id = edu_assignments1.assignment_id # and student_id = $user_id) # and edu_assignments.assignment_id = sol.task_id # union # select edu_assignments.assignment_name, # edu_assignments.assignment_id, # edu_assignments.due_date, # pset.version_id, # pset.file_extension, # pset.url, # '' as sol_url, # '' as sol_file_extension, # '' as sol_version_id, # '' as ans_url, # '' as ans_file_extension, # '' as ans_filename, # '' as ans_version_id, # edu_assignments.electronic_submission_p # from edu_assignments, # edu_assignments edu_assignments1, # fs_versions_latest pset # where edu_assignments.class_id = $class_id # and user_has_row_permission_p($user_id, 'read', pset.version_id, 'FS_VERSIONS') = 't' # and edu_assignments1.assignment_id = edu_assignments.assignment_id # and edu_assignments.file_id = pset.file_id # and not exists (select 1 from student_answers # where task_id = edu_assignments1.assignment_id # and student_id = $user_id) # and not exists (select 1 from solution_files # where task_id = edu_assignments.assignment_id) # union
