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.). After adding this Derived Table, you will be able to include a Roles field to your Views that displays the LMS Role of a user, as shown in the following image:

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 PUBLIC_READ role. 

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 holdingCommand/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 from the Right Tableselect 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 from the Right Table 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.
10. In the Sets and Items panel, select the newly added role item.
11. In the Properties panel, click the Edit button. The property fields of the role item become editable.
12. In the Label field, rename the roles item to "Role", for presentation purposes.
13. Click the Save button to save your changes to the Role item.
14. Click the OK button to save your changes in the Domain Designer and return to the Edit Domain page.
15. Under the Required Information heading, rename the Course Completions Domain in the Name field, in order to avoid confusing it with the Public Course Completions Domain. We will call this Domain the "Course Completions Domain - Roles".
16. To save your changes to the Domain, click the Submit button.