Create SQL queries for getting marks (Moodle 2.62)

Create SQL queries for getting marks (Moodle 2.62)

by Sebastián Pizarro Cortés -
Number of replies: 1

Exist a lot of topic about the different queries for looking marks in SQL database but neither specified the version of Moodle. Actually i have a Moodle v2.62 I'm trying to get a student's grades with a query but I can't work out joins. 

The query below (tables found it in stackoverflow).

Default:

SELECT mdl_grade_items.id AS ItemID,
       mdl_course.shortname AS CourseShortname,
       mdl_grade_items.itemname AS ItemName,
       mdl_grade_items.grademax AS ItemGradeMax,
       mdl_grade_items.aggregationcoef AS ItemAggregation,
       mdl_grade_grades.finalgrade AS FinalGrade,
       mdl_user.username AS StudentID,
       mdl_user.id
FROM mdl_grade_items
  INNERJOIN mdl_grade_grades 
    ON mdl_grade_items.id = mdl_grade_grades.itemid
  INNERJOIN mdl_role_assignments 
    ON mdl_grade_grades.userid = mdl_role_assignments.userid 
    AND mdl_grade_items.courseid = mdl_role_assignments.mdlcourseid
  INNERJOIN mdl_course 
    ON mdl_course.id = mdl_grade_items.courseid
  INNERJOIN mdl_user 
    ON mdl_user.id = mdl_role_assignments.userid
WHERE mdl_grade_items.courseid ='2864'AND mdl_user.username ='123456789'

The other user recommended this query.

But the problem i don`t know. What's do you refer with: userid; studentid; itemname; itengrademax; itemaggregation, courseid??

SELECT 
  u.id AS userid,
  u.username AS studentid,
  gi.id AS itemid,
  c.shortname AS courseshortname,
  gi.itemname AS itemname,
  gi.grademax AS itemgrademax,
  gi.aggregationcoef AS itemaggregation,
  g.finalgrade AS finalgrade
  FROM mdl_user u
  JOIN mdl_grade_grades g ON g.userid = u.id
  JOIN mdl_grade_items gi ON g.itemid =  gi.id
  JOIN mdl_course c ON c.id = gi.courseid
  WHERE gi.courseid  =:courseid AND u.username =:username
Average of ratings: -
In reply to Sebastián Pizarro Cortés

Re: Create SQL queries for getting marks (Moodle 2.62)

by Kathir Rajaraman -

Hi Sebastain

$sql = 'Your Sql Query';

You will fetch the single record from database below the code you will use,
$result = $DB->get_record_sql($sql); 

or
You will fetch the multiple records from database below the code you will use,

$results = $DB->get_records_sql($sql); 

For Example,
$sql = 'SELECT * FROM mdl_user';
$result = $DB->get_record_sql($sql);

Thanks
Kathir
Average of ratings: -