How do you extract grade from grading_standards.data where scores.course_score = true?

[Pete5484]

Wondering if anyone can suggest how to extract the equivalent of the grade book total letter grade?

In the scores table, scores.course_score is a boolean value where true "represents the overall score for the course referred to in the enrollment".

But it doesn't provide the letter grade. Instead it just links to the grading_standards table where the grading scheme is stored in grading_standards.data as json. 

The submissions table contains submissions.grade ("Letter grade mapped from the score by the grading scheme"). Why not do this for course _score? And override_score? It's in the rest api. 

It just seems unnecessarily complicated.

Anyway, wondering if anyone has any tips on doing this in SQL? 

[Pete5484]

Ok. So part way there! I've got the extract from the course grading scheme working.  

But would appreciate any advice on getting this to work with assignment submissions!

With the course grading scheme I'm not entirely sure if points are ever used! And i and I cant seem to create any gs.version = 1 schemes from within the gui - everything is version 2. So this works, so far...

...
,(
        SELECT grade->>0
        FROM jsonb_array_elements(CAST(gs.data AS jsonb)) AS grade
        WHERE 
            CASE
                WHEN sc.override_score IS NOT null THEN sc.override_score/100
                WHEN c.group_weighting_scheme IN ('percent', 'equal') THEN sc.current_score/100
                WHEN c.group_weighting_scheme IS null THEN sc.current_points
                ELSE NULL
            END >= (grade->1)::numeric 
        ORDER BY (grade->1)::numeric DESC
        LIMIT 1
    ) AS final_grade

FROM
    canvas.scores sc
JOIN
    canvas.enrollments e ON e.id = sc.enrollment_id 
    JOIN canvas.courses c ON e.course_id = c.id
    JOIN canvas.users u ON u.id = e.user_id
    JOIN canvas.pseudonyms p on p.id = u.id
    JOIN canvas.grading_standards gs ON gs.id = c.id
WHERE ...
AND sc.course_score IS true

But I get an Operator does not exist: character varying ->> integer when I try and apply this to assignments with a grading_type = letter grades.

select  a.id
, a.workflow_state
, a.points_possible
, a.grading_type
, a.description
, a.position
, a.title
, s.score
, s.score/a.points_possible AS score_decimal
, gs.data
,(
        SELECT grade ->>0
        FROM jsonb_array_elements(CAST(gs.data AS jsonb)) AS grade
        WHERE 
        (s.score/a.points_possible) >= (grade->1)::numeric 
        ORDER BY (grade->1)::numeric DESC
        LIMIT 1
    ) AS final_grade

from canvas.submissions s
join canvas.assignments a on a.id = s.assignment_id
left join canvas.grading_standards gs on a.grading_standard_id = gs."id" 
where grading_type = 'letter_grade'

 Any assistance appreciated.

[stimme]

@Pete5484 This is an interesting problem. I have not yet worked on deriving letter grades from the canvas.grading_standards table. Thanks for sharing your queries!

I'd advise reviewing the joins in your course-level query; the joins for canvas.pseudonyms & canvas.grading_standards appear to have mismatched keys. 

For your assignment-level query, my guess is that null values are causing the operator error. Since you're doing a left join (correctly, I think), the gs.data field will be null wherever a.grading_standard_id is null. To get results for the current query, you might be able to replace gs.data with COALESCE(gs.data,"") or COALESCE(gs.data,"[]").

There is a larger issue that you might be encountering. The Default Canvas Grading Scheme is not available in the grading_standards table (or via the Canvas API for that matter). I'm finding that a.grading_standard_id is null for the majority of assignments where grading_type = 'letter_grade', and I believe it's because they use Default Canvas Grading Scheme. The minimum values and letters in that scheme are shown in the Admin > Grading area in the GUI.

[Pete5484]

I pretty much just needed to rename the grade variable!

...

, s.score

, s.grade

, ( SELECT svalue->>0

FROM jsonb_array_elements(CAST(gs.data AS jsonb)) AS svalue

WHERE

(s.score/a.points_possible) >= (svalue->>1)::numeric

ORDER BY (svalue->>1)::numeric DESC

LIMIT 1

) AS submission_grade

... and then... I remembered that s.grade already contains the letter grade... so it wasn't really necessary for the submissions table. Only to grab the course grade! 🙄