Moodle | Fetch all the categories based on the programs assigned to a user

|
| By Webner

Below are the Moodle database tables that will be used to fetch all the categories based on the programs assigned to a user (complete query is also written after table information):

1. mdl_prog : This table contains each program along with the category to which program belongs, its summary, fullname and many other fields.

2. mdl_course_categories : This table contains all the categories along with different fields like name, description, sortorder etc .

3. mdl_prog_assignment : This table contains program id, assignment type – means whether assignment is assigned as an individual (5), audience (1), organisation etc.

4. mdl_cohort_members : This table contains cohortid and userid which implies many relations of audience with users.

5. mdl_cohort : This table contains all the audience and its related information .

This is the query:

$categorySql = 'SELECT s.* from (SELECT mdl_course_categories.id, mdl_course_categories.description, mdl_course_categories.name, mdl_course_categories.sortorder FROM mdl_prog_assignment JOIN mdl_cohort_members ON mdl_cohort_members.cohortid= mdl_prog_assignment.assignmenttypeid JOIN mdl_prog ON mdl_prog.id = mdl_prog_assignment.programid JOIN mdl_course_categories ON mdl_course_categories.id = mdl_prog.category WHERE mdl_prog_assignment.assignmenttype=3 AND mdl_cohort_members.userid='.$USER->id.' UNION SELECT mdl_course_categories.id, mdl_course_categories.description, mdl_course_categories.name, mdl_course_categories.sortorder FROM mdl_prog_assignment JOIN mdl_prog ON mdl_prog.id = mdl_prog_assignment.programid JOIN mdl_course_categories ON mdl_course_categories.id = mdl_prog.category  WHERE mdl_prog_assignment.assignmenttype=5 AND mdl_prog_assignment.assignmenttypeid ='.$USER->id.' ) s ORDER BY s.sortorder';

$categoryList= $DB->get_records_sql($categorySql);

Webner Solutions is a Software Development company focused on developing CRM apps (Salesforce, Zoho), LMS Apps (Moodle/Totara), Websites and Mobile apps. If you need LMS app development or any other software development assistance please contact us at lms@webners.com

Leave a Reply

Your email address will not be published. Required fields are marked *