Case Study: Adding User's Role to Domain

This case study will cover adding and configuring a Derived Table to a Domain that identifies the LMS Role of the user in the Table (e.g. student, editing teacher, etc.).

For the purposes of this case study, we will add the Roles Derived Table to the Course Completions Domain. In order to edit the Domain, you will need to copy it from the Public folder of the Repository to a folder in your private organizational folder tree —copying the Domain requires you to have the Zoola To copy the Course Completions Domain for editing, complete the following steps:

1. In the Repository, navigate to Public > Moodle/Totara > Domains.
2. Right-click the Course Completions Domain and select Copy.
3. Navigate to the Domains folder of your private organizational tree.
4. Right click the Domains folder and select Paste. An editable copy of the Course Completions Domain now exists in your private Domains folder.

To edit the Course Completions Domain and add and configure the Roles Derived Table, complete the following steps:

1. Right-click your newly copied Course Completions Domain and select Edit. The Edit Domain page appears.
2. Below the Domain Design heading, click the Edit with Domain Designer link. The Domain Designer opens to the Display tab.
3. Navigate to the Derived Tables tab:
   
    
   
   
4. The 'roles' Derived Table will pull user roles from the mdl_role_assignments and mdl_context tables. To create this Derived Table, complete the following steps:
   1. 1. 1. In the Query ID field, enter "roles".

         2. In the Query field, enter the following SQL query:
            
            

            select distinct ra.userid, ctx.instanceid as courseid, ra.roleid, if(length(r.name) > 0, r.name, r.shortname) as role
            from mdl_role_assignments ra
            join mdl_context ctx on (ra.contextid = ctx.id and ctx.contextlevel = 50)
            join mdl_role r on ra.roleid = r.id

         3. Click the Run Query button.
         4. A list of query results will appear below the Run Query button. Select all of the resulting fields by holding Command/CTRL/Shift and selecting each individual field:
            
            
         5. Click the Save Table button. Your new "roles"  Derived Table will now appear in the Available Objects panel with the unique Derived Table/Calculated Field identifier:
            
            
5. Navigate to the Joins tab and create the following joins:
   1. 1. 1. From the Left Table, select the course field from the mdl_course_completions table, then select the courseid field from the roles Derived Table. Click the Inner Join button.
         2. From the Left Table, select the userid field from the mdl_course_completions table, then select the userid field from the roles Derived Table. Click the Inner Join button.
         3. A modal window appears, notifying you that the tables have already been joined, and that a composite key will be created. Click the OK button to complete your second join: 
            
            
6. Navigate to the Display tab.
7. In the Resources panel, select to View as: Join Tree.
8. Expand JoinTree_1, then expand the roles table:
   
    
9. Select the role field and drag it into the Course Completions set in the Sets and Items panel.