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

[Ray Davis]

It's unsolicited advice, but I have to second Lance's comment: this 
sounds extraordinarily dangerous. Duplicate implementations are never a 
great idea, and for some reason students and instructors tend to get 
especially irked by discrepancies in grade displays. Even if you manage 
to exactly replicate today's logic, who's going to ensure that your SQL 
stays up to date with every future change and bug-fix?

For whatever my opinion's worth, if I needed Gradebook data that wasn't 
available through existing Gradebook service methods, I'd formally 
request a new service method that exposed the existing internal 
functionality; if I needed it in a hurry, I'd branch the code in msub 
and submit a patch along with my JIRA request.

Best,
Ray

On 12/1/09 11:39 AM, will at serensoft.com wrote:
> Hold the phone, we're closing in on it! So far this appears to work for 
> the per-gradable-object level (that is, course-grade-overrides are 
> ignored at this point, that'll come in a later phase)...
> 
> If you have gradebook chops, see if the following SQL is missing any 
> tricks, our spot-checking indicates it's working! (Seems to work for 
> no-category-grades, category-only-grades, and weighted-category-grades...)
> 
> Note that the temporary tables all start with "x_" to avoid colliding 
> with any existing Sakai tables, and they "go away" once you close your 
> MySQL connection.
> 
> 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 'SOME-FILTERING-HERE%'
> ;
> 
> drop table if exists x_categories;
> 
> create temporary table x_categories
> 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,
>     sum( o.points_possible ) max,
>     sum( r.points_earned ) pts
> from
>     (
>     GB_GRADABLE_OBJECT_T o
>         left join
>     GB_CATEGORY_T c
>         on o.CATEGORY_ID = c.ID
>         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 *****
> group by
>     s.title,
>     s.site_id,
>     g.id <http://g.id>,
>     u.eid,
>     u.user_id,
>     c.weight,
>     c.name <http://c.name>
> ;
> 
> -- intermediate quick-check, already rolled up into categories when 
> available:
> select worksite,username,category,weight,max,pts,
>     (pts/max) score,
>     (pts/max*weight) weighted
> from x_categories;
> 
> -- now for the real grades (again these are gradable-object level grades,
> -- if any course-override grades have been entered they would be ignored
> select
>     worksite,
>     -- site_id,
>     username,
>     -- user_id,
>     sum( weight ) scale,
>     case
>         when sum(weight) = 0 or sum(weight) is null
>             then sum( pts ) / sum( max )
>         else sum( pts/max * weight ) / sum( weight )
>         end score
> from
>     x_categories
> group by
>     worksite,
>     username;
> 
> 
> 
>     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.
> 
> 
> 
> -- 
> will trillich
> "Tis the set of the sails / And not the gales / That tells the way we 
> go." -- Ella Wheeler Wilcox
> 
>