Tips Basis Data Moodle

1. Cara mengganti user pada pekerjaan Quiz (bisa dilakukan di tengah mengerjakan atau setelah selesai)

   

   1. update moodle_quiz_attempts set userid=(select id from moodle_user where username = ?usernamebaru) where quiz = (select instance from moodle_course_modules where id=?idmodul) and userid=(select id from moodle_user where username = ?usernamelama)
   2. update moodle_quiz_grades set userid=(select id from moodle_user where username = ?usernamebaru) where quiz = (select instance from moodle_course_modules where id=?idmodul) and userid=(select id from moodle_user where username = ?usernamelama)
2. Cara mendapatkan peserta
   Ketahui terlebih dahulu id dari masing-masing module dengan perintah:
   SELECT * FROM moodle_modules;
   1. Forum
      CREATE ALGORITHM = MERGE DEFINER = `root`@`localhost` SQL SECURITY DEFINER
      VIEW `elearning_pesertaforum` AS
      SELECT  `fp`.`id` AS `idpost`, `fd`.`forum` AS `idforum`, `u`.`username` AS `username`, `fd`.`course` AS `idcourse`, `cm`.`id` AS `idmoduleelearning`
      FROM
      (((`moodle_forum_discussions` `fd`
      JOIN `moodle_forum_posts` `fp` ON ((`fd`.`id` = `fp`.`discussion`)))
      JOIN `moodle_user` `u` ON ((`u`.`id` = `fp`.`userid`)))
      JOIN `moodle_course_modules` `cm` ON ((`cm`.`instance` = `fd`.`forum`)))
      WHERE
      (`cm`.`module` = 5)
   2. Kuis/Quiz
      CREATE ALGORITHM = MERGE DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `elearning_pesertakuis` AS
      SELECT `qa`.`id` AS `idpengerjaanpertama`, `qa`.`quiz` AS `idkuis`, `u`.`username` AS `username`, `q`.`course` AS `idcourse`, `cm`.`id` AS `idmoduleelearning`
      FROM
      (((`moodle_quiz` `q`
      JOIN `moodle_quiz_attempts` `qa` ON ((`qa`.`quiz` = `q`.`id`)))
      JOIN `moodle_user` `u` ON ((`u`.`id` = `qa`.`userid`)))
      JOIN `moodle_course_modules` `cm` ON ((`cm`.`instance` = `qa`.`quiz`)))
      WHERE
      ((`cm`.`module` = 12) AND (`qa`.`attempt` = 1))
   3. Penugasan/Assignment
      CREATE ALGORITHM = MERGE DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `elearning_pesertapenugasan` AS
      SELECT `asb`.`id` AS `idkumpul`, `asb`.`assignment` AS `idpenugasan`, `u`.`username` AS `username`, `a`.`course` AS `idcourse`, `cm`.`id` AS `idmoduleelearning`
      FROM
      (((`moodle_assign` `a`
      JOIN `moodle_assign_submission` `asb` ON ((`asb`.`assignment` = `a`.`id`)))
      JOIN `moodle_user` `u` ON ((`u`.`id` = `asb`.`userid`)))
      JOIN `moodle_course_modules` `cm` ON ((`cm`.`instance` = `asb`.`assignment`)))
      WHERE
      ((`asb`.`status` = ‘submitted’) AND (`cm`.`module` = 25))

Demikian, semoga bermanfaat. [bst]