Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-69142

Unknown column 'rolename' in 'order clause' in Assign user roles to cohort

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide
      1. Login as admin in your Moodle.
      2. Create five new users named u1, u2, u3 and u4.
      3. Go to Site administration > Users > Accounts > Cohorts and Add a new cohort named cohort1.
      4. Go to Site administration > Users > Permissions > Define roles
      5. edit the Non-editing teacher role
      6. Set the "Context types where this role may be assigned" to User, Course, Activity module
      7. Click on Save changes.
      8. Go to Site administration > Users > Permissions > Assign user roles to cohort
        1. In "Select users to assign role" field select the user u1, u2, u3 and u4.
        2. In "Select role" field select the Non-editing teacher role.
        3. In "Select cohorts" field select the cohort1 
        4. Click on Assign button.
      9. Expected result: Confirm you could see a list of existing cohort role assignments with four rows.
      10. Expected result: Confirm you could not click on "Role" column to sort the list.
      11. Expected result: Confirm you could click on "Email address" column to sort the list and the list is sorted.

       

      Show
      Login as admin in your Moodle. Create five new users named u1, u2, u3 and u4. Go to Site administration > Users > Accounts > Cohorts and Add a new cohort named cohort1. Go to Site administration > Users > Permissions > Define roles edit the Non-editing teacher role Set the "Context types where this role may be assigned" to User, Course, Activity module Click on Save changes. Go to Site administration > Users > Permissions > Assign user roles to cohort In "Select users to assign role" field select the user u1, u2, u3 and u4. In "Select role" field select the Non-editing teacher role. In "Select cohorts" field select the cohort1  Click on Assign button. Expected result: Confirm you could see a list of existing cohort role assignments with four rows. Expected result: Confirm you could not click on "Role" column to sort the list. Expected result: Confirm you could click on "Email address" column to sort the list and the list is sorted.  
    • Affected Branches:
      MOODLE_310_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
    • Pull 3.8 Branch:
    • Pull 3.9 Branch:
    • Pull Master Branch:
      MDL-69142-master
    • Sprint:
      Moppies Kanban

      Description

      When you try to order the list of existing cohort role assignments by role column you get this error:

       

      Debug info:  Unknown column 'rolename' in 'order clause'
      SELECT uca.id, uca.cohortid, uca.userid, uca.roleid, c.name as cohortname, c.idnumber as cohortidnumber, c.contextid as cohortcontextid, c.visible as cohortvisible, c.description as cohortdescription, c.theme as cohorttheme, u.email, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname
       FROM mdl_tool_cohortroles uca
       JOIN mdl_user u ON u.id = uca.userid
       JOIN mdl_cohort c ON c.id = uca.cohortid ORDER BY rolename DESC, lastname ASC LIMIT 0, 50
      [array (
      )]
      Error code: dmlreadexception

       

      How to replicate

      1. Log as an admin.
      2. Go to Site administration / Users / Permissions / Define roles
      3. Edit a Non-editing teacher role and check the User context in "Context types where this role may be assigned"
      4. Save changes and go to Site administration / Users / Accounts / Cohorts
      5. Add a new cohort.
      6. Go to Site administration / Users / Permissions / Assign user roles to cohort
      7. And select two users in "Select users to assign role "
      8. Select "Non-editing teacher" in Select role dropdown.
      9. And select the new cohort in Select cohort and click on Assign button.
      10. After that you could see a list of existing cohort role assignments.
      11. Click on "Role" column to sort the list.
      12. Expected result:
        1. The list of existing cohort role assignments will be sorted or not having the possibility to order by role since it does not add value to the list of items.
      13. Current result:
        1. Error reading from database.

      Notice: 

      Checking the code, I think originally someone forgot to indicate that the role column was not sortable. This is because using table_sql you cannot sort by the role name as it is currently developed. It is not easy to sort through SQL a list by role name considering that role names are localized for a context. See role_get_names] function.

      So, maybe the fix should be don't let the user sort by role column like other parts in Moodle.

        Attachments

          Activity

            People

            Assignee:
            cescobedo Carlos Escobedo
            Reporter:
            cescobedo Carlos Escobedo
            Peer reviewer:
            Ferran Recio
            Integrator:
            Andrew Nicols
            Participants:
            Component watchers:
            Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 2 hours, 20 minutes
                2h 20m