[Building Sakai] Samigo - get answers sql

[Brian Jones]

I'm not sure what version you're using, or if the table structure/data
differ between versions, but here are the queries we use to get submissions
and answers:

How to find the submission details for a student in tests and quizzes
=====================================================================

1) find the internal user id (sakai_user_id_map)

select * from sakaiadmin.sakai_user_id_map where eid = '<user_login>'

2) find the assessment id (sam_accesscontrol_t)

select * from sakaiadmin.sam_assessaccesscontrol_t where releaseto like
'%<site_title>%';

3) find the submission(s) for the user

select * from sakaiadmin.sam_publishedassessment_t pa, 
sakaiadmin.sam_assessmentgrading_t ag 
where pa.id = ag.publishedassessmentid 
and pa.id = '<assessment_id_from_above>' and ag.agentid =
'<user_id_from_above>'

4) find the answer(s) for the user for that submission

select * from sakaiadmin.sam_itemgrading_t 
where sakaiadmin.sam_itemgrading_t.assessmentgradingid =
'<assessmentgradingid_from_above_query>'

Brian Jones
Applications Development
Information Technology Services
Support Services Building, Room 4326
Western University
(519) 661-2111 x86969
bjones86 at uwo.ca

From: sakai-dev-bounces at collab.sakaiproject.org
[mailto:sakai-dev-bounces at collab.sakaiproject.org] On Behalf Of Wheat, Jon
Sent: Wednesday, December 05, 2012 11:35 AM
To: SAKAI Dev
Subject: [Building Sakai] Samigo - get answers sql

I'm struggling trying pull together the tables needed to list answers for a
particular assessment for a student.
I've found the proper entry in SAM_ASSESSMENTGRADING_T but attempting to
piece together the other tables (which I'm not sure of) is somewhat mind
boggling.

Would anyone have some sql they could post that would either list all
answers for a students for a particular assessment, or give some pointers as
to which tables I should be using?  I'm guessing SAM_ANSWER_T, but I can't
figure out how to tie that back properly

Thanks,
 -Jon

==============================================
Jonathan Wheat, Ph.P.
Messiah College, Information Services Group 
Spam me: jwheat at messiah.edu 
Stalk me : @jonathanpwheat 
Read me : codedog.net