0

**Edited

I split the sql queries into 3 and now the queries look like this.

$sql_2 = "SELECT DISTINCT cr.id AS courseid,
        cr.fullname AS coursename,
        cr.idnumber AS idnumber,
        COUNT(DISTINCT ra.id) AS enrols, 
        COUNT(DISTINCT cc.timecompleted) AS completed 
        FROM {course} cr JOIN {context} ct ON ( ct.instanceid = cr.id ) 
        LEFT JOIN {role_assignments} ra ON ( ra.contextid = ct.id ) and ra.roleid = 5 
        LEFT JOIN {course_completions} cc ON (cc.course = cr.id) 
        WHERE cr.id = :course_id GROUP BY cr.fullname, cr.id";

$sql_3 = "SELECT DISTINCT 
        u.id AS userid,
        u.email AS email,
        u.lastaccess,
        c.id AS courseid
        FROM {user} u
        JOIN {role_assignments} ra ON ra.userid = u.id
        LEFT JOIN {context} ct ON ct.id = ra.contextid AND ra.roleid = 5
        LEFT JOIN {course} c ON c.id = ct.instanceid AND ct.contextlevel = 50
        WHERE c.id = :course_id";

$sql_4 = "SELECT DISTINCT 
        u.id AS userid,
        sc.name AS item,
        m.name AS module,
        cm.id AS cmid,
        cm.idnumber AS itemidnumber,
        sc.launch AS launch,
        cmc.completionstate AS completed,
        cmc.timemodified AS timecompleted
        FROM {user} u
        JOIN {role_assignments} ra ON ra.userid = u.id
        LEFT JOIN {context} ct ON ct.id = ra.contextid AND ra.roleid = 5
        LEFT JOIN {course} c ON c.id = ct.instanceid AND ct.contextlevel = 50
        LEFT JOIN {course_modules} cm ON cm.course = c.id
        LEFT JOIN {course_modules_completion} cmc ON cmc.userid = u.id AND cmc.coursemoduleid = cm.id
        LEFT JOIN {modules} m ON m.id = cm.module
        LEFT JOIN {scorm} sc ON sc.id = cm.instance
        WHERE c.id = :course_id and m.id = 18";

$courses = $DB->get_records_sql($sql_2, ["course_id" => $requestedcourseids]);
$users = $DB->get_records_sql($sql_3, ["course_id" => $requestedcourseids]);
$modules = $DB->get_records_sql($sql_4, ["course_id" => $requestedcourseids]);

I added foreach and for loop under the each result to get the multidimensional array result and got the module results like this

   "course": [
{
"courseid": 4,
"coursename": "Activities",
"idnumber": "",
"enrols": 6,
"completed": 1,
"users": [
    {
        "userid": 3,
        "email": "test1@hotmail.com",
        "lastaccess": 1599379804,
        "modules": [
            {
                "userid": 3,
                "module": "scorm",
                "item": "Vitamin",
                "itemidnumber": "",
                "cmid": 24,
                "launch": 28,
                "completed": 1,
                "timecompleted": 1595575023
            },
            {
                "userid": 2,
                "module": "scorm",
                "item": "Vitamin",
                "itemidnumber": "",
                "cmid": 24,
                "launch": 28,
                "completed": 0,
                "timecompleted": 1593135761
            },
            {
                "userid": 4,
                "module": "scorm",
                "item": "Vitamin",
                "itemidnumber": "",
                "cmid": 24,
                "launch": 28,
                "completed": 1,
                "timecompleted": 1589359945
            },
            {
                "userid": 8,
                "module": "scorm",
                "item": "Vitamin",
                "itemidnumber": "",
                "cmid": 24,
                "launch": 28,
                "completed": null,
                "timecompleted": null
            },
            {
                "userid": 7,
                "module": "scorm",
                "item": "Vitamin",
                "itemidnumber": "",
                "cmid": 24,
                "launch": 28,
                "completed": null,
                "timecompleted": null
            }
        ]
    },
    {
        "userid": 2,
        "email": "test2@gmail.com",
        "lastaccess": 1599635370,
        "modules": [
            {
                "userid": 9,
                "module": "scorm",
                "item": "Vitamin",
                "itemidnumber": "",
                "cmid": 24,
                "launch": 28,
                "completed": 1,
                "timecompleted": 1595576987
            },
            {
                "userid": 3,
                "module": "scorm",
                "item": "Vitamin",
                "itemidnumber": "",
                "cmid": 24,
                "launch": 28,
                "completed": 1,
                "timecompleted": 1595575023
            },,,,

As you can see the module list only displays one module with each userid.

It should list 8 different modules per user but it loops only 6 times that is only the number of user count. I'd like to get users with all modules the user attempted.

Here is the code

foreach($courses as $completion) {

if(!is_null($course)) {
    $coursesdata[] = $course;
}
$course = array();
$course['courseid'] = $completion->courseid;
$course['coursename'] = $completion->coursename;
$course['idnumber'] = $completion->idnumber;
$course['enrols'] = $completion->enrols;
$course['completed'] = $completion->completed;
$course['users'] = [];

if(!empty($users)) {
    foreach($users as $userinfo) {
        for($i=0; $i<count($users); $i++) {
            $user = array();
            $user['userid'] = $userinfo->userid;
            $user['email'] = $userinfo->email;
            $user['lastaccess'] = $userinfo->lastaccess;
            $user['modules'] = array();

            if(!empty($modules)) {
                foreach($modules as $details) {
                    for($i=0; $i<count($modules); $i++) {
                        $module = array();
                        $module['module'] = $details->module;
                        $module['item'] = $details->item;
                        $module['itemidnumber'] = $details->itemidnumber;
                        $module['cmid'] = $details->cmid;
                        $module['launch'] = $details->launch;
                        $module['completed'] = $details->completed;
                        $module['timecompleted'] = $details->timecompleted;
                    }
                    if(!is_null($module)) {
                        $user['modules'][] = $module;
                    }
                }
            }
        }
        if(!is_null($user)) {
            $course['users'][] = $user;
        }
    }
}
}

Question: How can I put all data from $modules under $users array? At the moment it only loops the first element in $modules query.

I've tried methods suggested in the comments and answer but this is the closest one that I got so far.

Any help or tips would be appreciated.

Thanks in advance!

Lucy Hong
  • 91
  • 8
  • (1)what is "enrols"? (2)can you update the quetion with relevant tables and its relations? – ariefbayu Sep 10 '20 at 01:42
  • For ideas: https://www.geeksforgeeks.org/how-to-create-an-array-for-json-using-php/ and https://stackoverflow.com/questions/19454208/multidimensional-array-php-json and many others. – Nic3500 Sep 10 '20 at 02:45
  • @ariefbayu Thanks for the comment. I've edited a bit hope this makes sense to you now. – Lucy Hong Sep 10 '20 at 09:38
  • What a long question, so many lines of code you ask us to look at. An important way to find your issues is to split your problem in half again and again, to reduce it to a few lines that do not work. – PaulH Sep 13 '20 at 07:07

2 Answers2

1

First your current code is vulnerable to sql injection, you really should use parameters Moodle Documentation. I'd also break modules out into another query.

$sql_2 = "SELECT DISTINCT cr.id AS courseid,
    cr.fullname AS coursename,
    cr.idnumber AS idnumber,
    COUNT(DISTINCT ra.id) AS enrols, 
    COUNT(DISTINCT cc.timecompleted) AS completed 
    FROM {course} cr JOIN {context} ct ON ( ct.instanceid = cr.id ) 
    LEFT JOIN {role_assignments} ra ON ( ra.contextid = ct.id ) and ra.roleid = 5 
    LEFT JOIN {course_completions} cc ON (cc.course = cr.id) 
    WHERE cr.id = :course_id GROUP BY cr.fullname, cr.id";

 $sql_3 = "SELECT DISTINCT 
        u.id AS userid,
        u.email AS email,
        c.id AS courseid
        FROM {user} u
        JOIN {role_assignments} ra ON ra.userid = u.id
        LEFT JOIN {context} ct ON ct.id = ra.contextid AND ra.roleid = 5
        LEFT JOIN {course} c ON c.id = ct.instanceid AND ct.contextlevel = 50
        WHERE c.id = :course_id and m.id = 18 ORDER BY u.email";
        
$sql_4 = "SELECT DISTINCT 
        u.id AS userid,
        m.name AS module
        FROM {user} u
        JOIN {role_assignments} ra ON ra.userid = u.id
        LEFT JOIN {context} ct ON ct.id = ra.contextid AND ra.roleid = 5
        LEFT JOIN {course} c ON c.id = ct.instanceid AND ct.contextlevel = 50
        LEFT JOIN {course_modules} cm ON cm.course = c.id
        LEFT JOIN {course_modules_completion} cmc ON cmc.userid = u.id AND cmc.coursemoduleid = cm.id
        LEFT JOIN {modules} m ON m.id = cm.module
        LEFT JOIN {scorm} sc ON sc.id = cm.instance
        WHERE c.id = :course_id and m.id = 18 ORDER BY u.email";

$courses = $DB->get_records_sql($sql_2, ["course_id" => $requestedcourseids]);
$users = $DB->get_records_sql($sql_3, ["course_id" => $requestedcourseids]);
$modules = $DB->get_records_sql($sql_4, ["course_id" => $requestedcourseids]);

$course_user_modules = [];

foreach($courses as $course){
    $course["users"] = [];
    foreach($users as $course_user){
        if($course["courseid"]==$course_user["courseid"]){
            unset($course_user["courseid"]);
            $course_user["modules"] = [];
            foreach($modules as $user_module){
                if($user_module["userid"]==$course_user["userid"]){
                    unset($user_module["userid"]);
                    array_push($course_user["modules"], $user_module);
                }
            }
            array_push($course["users"], $course_user);
        }
    }
    array_push($course_user_modules, $course);
}
Jpsh
  • 1,697
  • 12
  • 17
  • Thank you so much for the answer. I've broken out the query into two and tested the code, but I get `"message": "Cannot use object of type stdClass as array"`. I've looked at https://stackoverflow.com/questions/6815520/cannot-use-object-of-type-stdclass-as-array to resolve this but I couldn't. Is there anything else that I should try? – Lucy Hong Sep 10 '20 at 23:25
  • You are using array operators on an object. Please show us the line of code corresponding to your error. – PaulH Sep 13 '20 at 07:09
1

I found the issue in Moodle database calling get_records_sql

$modules = $DB->get_records_sql($sql_4, ["course_id" => $requestedcourseids]);

This returns an array indexed by the first field in the results. Which means in my case, only returns one set of an array from each userid. After I changed the sql query for moduleid(unique value) to come first it is now working as expected.

$sql_4 = "SELECT DISTINCT   cm.id AS moduleid,
                            u.id AS userid,
                            sc.name AS item,
                            m.name AS module,
                            cm.idnumber AS itemidnumber,
                            sc.launch AS launch,
                            cmc.completionstate AS completed,
                            cmc.timemodified AS timecompleted
                            FROM {user} u
                            JOIN {role_assignments} ra ON ra.userid = u.id
                            LEFT JOIN {context} ct ON ct.id = ra.contextid AND ra.roleid = 5
                            LEFT JOIN {course} c ON c.id = ct.instanceid AND ct.contextlevel = 50
                            LEFT JOIN {course_modules} cm ON cm.course = c.id
                            LEFT JOIN {course_modules_completion} cmc ON cmc.userid = u.id AND cmc.coursemoduleid = cm.id
                            LEFT JOIN {modules} m ON m.id = cm.module
                            LEFT JOIN {scorm} sc ON sc.id = cm.instance
                            WHERE c.id = :course_id and m.id = 18 and u.id = :userid";

Before it was like,

users:
    userid: 2,
    module:
      moduleid: 12,
    userid: 8,
    module:
      moduleid: 12
    userid: 9,
    module:
      moduleid: 12,

Now I get,

users:
    userid: 2,
    module:
      moduleid: 12,
      moduleid: 14,
      moduleid: 17,
    userid: 8,
    module:
      moduleid: 12,
      moduleid: 14,
      moduleid: 17,
    userid: 9,
    module:
      moduleid: 12,
      moduleid: 14,
      moduleid: 17,

This post helped me to sort it out. get_records_sql returns only one result using inner join in moodle

Lucy Hong
  • 91
  • 8