CSC/ECE 517 Spring 2014/oss E1404 mnp

Improvement to View Scores

Introduction

Background

The scores are calculated for multiple views. This code is very slow because:

• Separate db queries are used for each rubric that has been filled out by anyone associated with the assignment; these queries are made sequentially while the HTML page is being written
• HTML for the whole page is generated, largely by controller methods, before anything is displayed.

This code needs to be made faster.

What needs to be done

Classes

models/score.rb controllers/grades_controller.rb

Objectives

• Participant.find(user id).scores # should return a Hash or new object, with a single database query. This method should return the scores of a participant from all the assignments in which the participant has submitted.
• assignment.scores # should return a Hash or new object, with a single database query. This method should return scores of all the participants in an assignment.
• course.scores # should return a Hash. This method should return scores of all the participants enrolled in a course.

Design and implementation

Objective 1: Get scores of all the assignments for a given user

We needed to write a method and add a view that computed the scores of a user across all the assignments user has participated in. We added a new method all_scores in the grades_controller.rb. This method executes the following steps:

• Executes the below sql query:

"SELECT assignments.name, questionnaire_type, avg(score), min(score), max(score) FROM (SELECT r.id as response_id, team_id, tu.user_id as user_id, (SUM(weight*score)*100)/(sum(weight)*max_question_score) as score, parent_id as assignment_id, qs.type as questionnaire_type FROM scores s ,responses r, response_maps rm, questions q, questionnaires qs , teams_users tu , teams t WHERE rm.id = r.map_id AND r.id=s.response_id AND q.id = s.question_id AND qs.id = q.questionnaire_id AND tu.team_id = rm.reviewee_id AND tu.team_id = t.id group by r.id) as participant_score_aggregate, assignments WHERE assignments.id = participant_score_aggregate.assignment_id and user_id = ? group by assignment_id, questionnaire_type"

The user_id is of the current user is passed as a parameter to this query.

• From the result obtained from the above query, we create a hash of the format:

assignment name => questionnaire type

questionnaire type is again a hash that is of the form: questionnaire type => score_type

The score type is a hash that takes any of the three values [avg, max and min] as key the value will be the actual score.