[Building Sakai] GB: pulling grades, en masse, from gradebook via SQL

[will at serensoft.com]

Use case: the registrar wants to see how the students are doing, especially
those who might need remedial support.

We are experimenting with how best to pull grades from the gradebook in
2.6.x on MySQL v5.0. We have some SELECT statements that do some
grade-pulling... and some spot-checks indicate they match with what's shown
in the browser for simple cases, but with partial activity in weighted
categories, it gets muddy and muddier.

For example: a course has two weighted grading categories: alpha at 40%,
omega at 60%. Each weighted category has three items worth 5 points apiece.
When TWO items from "alpha" have been graded and NO items from "omega" have
had any activity at all, the student gets N out of 10 points (5 per graded
object, and there are only two that have been graded) as total course grade
at that point! How can we accomodate this via SQL?

Any suggestions and pointers are welcome! (Another tricky part is how to
distinguish between a course that has no weighted categories, and a course
that has some gradable objects not assigned to a weighted category?)


SQL so far for MySQL 5.1 and Sakai 2.6.x:

drop table if exists x_courses;

create temporary table x_courses
    select
        site.site_id,
        site.title worksite,
        p.value term
    from
        SAKAI_SITE site
            join
        SAKAI_SITE_PROPERTY p
            on site.SITE_ID = p.SITE_ID
    where
        p.value = 'fa09' and -- ***** SEMESTER FILTER HERE *****
        p.name = 'term_eid' and
        site.published = 1
        -- and site.title like 'BLAH%' -- other trim-down filters here
;

drop table if exists x_grades;

create temporary table x_grades
select
    s.title worksite,
    s.site_id,
    g.id gradebook,
    u.eid username,
    u.user_id,
    c.weight,
    c.name category,
    o.name graded_item,
    o.points_possible max,
    r.points_earned pts
from
    (
    GB_GRADABLE_OBJECT_T o
        left join
    GB_CATEGORY_T c
        on o.CATEGORY_ID = c.ID
        left join
    (
    GB_GRADE_RECORD_T r
        join
    SAKAI_USER_ID_MAP u
        on r.student_id=u.user_id
    )
        on o.id = r.gradable_object_id
    )
        join
    GB_GRADEBOOK_T g
        on g.id=o.gradebook_id
        join
    SAKAI_SITE s
        on g.gradebook_uid=s.site_id
where
    s.site_id in (
        select
            site_id
        from
            x_courses
    ) and
    o.object_type_id = 1 and -- ***** NOT 'COURSE GRADE', gradebook-items
only *****
    o.not_counted != 1 -- ***** OMIT IF NOT INCLUDED IN COURSE GRADE *****
order by
    s.title,
    u.eid,
    c.name
;

Then the trick is to "properly" sum grades by category... any help is
welcome!

-- 
will trillich
"Tis the set of the sails / And not the gales / That tells the way we go."
-- Ella Wheeler Wilcox
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20091130/5ecc5d9b/attachment.html