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

Fix participants filter GROUP BY issues in Oracle/MSSQL

    XMLWordPrintable

    Details

      Description

      There are some phpunit/behat fails in Oracle/MSSQL due to the use of GROUP BY in the participants filtering SQL which need to be addressed.

      After some discussions with Eloy, it appears that avoiding GROUP BY via joining on all users in the course will be the most performant approach, similar to the example below: 

      SELECT u.id, u.picture, u.firstname, targetusers.lastname,.....,
             COALESCE(ul.timeaccess, 0) AS lastaccess,
             ctx.id AS ctxid, ctx.path AS ctxpath, ....
      FROM
          (SELECT DISTINCT tu.id 
              FROM b1user tu
              JOIN b1user_enrolments ue ON ue.userid = tu.id
              JOIN b1enrol e ON e.id = ue.enrolid AND e.courseid = :o_courseid1
      ) targetusers
      JOIN b1user u ON u.id = targetusers.id
      LEFT JOIN b1user_lastaccess ul ON (ul.userid = targetusers.id AND ul.courseid = :o_courseid2)
      LEFT JOIN b1context ctx ON (ctx.instanceid = targetusers.id AND ctx.contextlevel = :o_contextlevel)
      <<OTHER FILTERS GO HERE>>
      ORDER BY xxxxxxx ASC 

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              michaelh Michael Hawkins
              Reporter:
              michaelh Michael Hawkins
              Peer reviewer:
              Sara Arjona (@sarjona)
              Integrator:
              Eloy Lafuente (stronk7)
              Tester:
              CiBoT
              Participants:
              Component watchers:
              Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                15/Jun/20

                  Time Tracking

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