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

[Speelmon, Lance Day]

IMHO, there are too many calculations occurring in Java to try to reproduce accurately in SQL.  Calling the service is probably the only sane option.  L


Lance Speelmon
Scholarly Technologist

On Dec 1, 2009, at 9:23 AM, will at serensoft.com<mailto:will at serensoft.com> wrote:

It's looking more and more like SQL alone won't do the trick. Must we fully recreate the init() logic in gradebook/app/ui/src/java/org/sakaiproject/tool/gradebook/ui/RosterBean.java to get a quick grade-snapshot? Yikes!

Sure would be great to have a webservice that returned grade sheets... :)


On Mon, Nov 30, 2009 at 10:32 PM, will at serensoft.com<mailto:will at serensoft.com> <will at serensoft.com<mailto:will at serensoft.com>> wrote:
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<http://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<http://g.id/> gradebook,
    u.eid username,
    u.user_id,
    c.weight,
    c.name<http://c.name/> category,
    o.name<http://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<http://o.id/> = r.gradable_object_id
    )
        join
    GB_GRADEBOOK_T g
        on g.id<http://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<http://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



--
will trillich
"Tis the set of the sails / And not the gales / That tells the way we go." -- Ella Wheeler Wilcox
_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org<mailto:sakai-dev at collab.sakaiproject.org>
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20091201/cacd89f7/attachment.html