A script to remove unused questions from Moodle question bank.

clearquestionbank.php

<?php
// This file is part of Moodle - http://moodle.org/
//
// Moodle is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// Moodle is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.

/**
 * A script to remove unused questions from Moodle question bank.
 *
 * @subpackage cli
 * @copyright  2024 Willian Mano <[email protected]>
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 */

define('CLI_SCRIPT', true);

require('config.php');
require_once("{$CFG->libdir}/clilib.php");

list($options, $unrecognized) = cli_get_params(
    [
        'help' => false,
        'number' => 1000,
    ], [
        'h' => 'help',
        'n' => 'number',
    ]
);

if ($unrecognized) {
    $unrecognized = implode("\n  ", $unrecognized);
    cli_error(get_string('cliunknowoption', 'admin', $unrecognized));
}

$help = <<<EOT
Question bank cleanup task.

Options:
 -h, --help                Print out this help
 -n, --number              Number of questions to clear

Run all queued tasks:
\$sudo -u www-data /usr/bin/php clearquestionbank.php --n=1000

EOT;

if ($options['help']) {
    echo $help;
    exit(0);
}

if (moodle_needs_upgrading()) {
    echo "Moodle upgrade pending, cron execution suspended.\n";
    exit(1);
}

// Process params.
core_php_time_limit::raise();

// Increase memory limit.
raise_memory_limit(MEMORY_EXTRA);

\core\local\cli\shutdown::script_supports_graceful_exit();
$humantimenow = date('r', time());
mtrace("Task start at: {$humantimenow}\n");

$numberofrecords = $options['number'] ?? 1000;

$sql = "SELECT id, name, qtype FROM {question}
        WHERE id NOT IN (
            SELECT q.id FROM {quiz_slots} slot
            INNER JOIN {question_references} qr ON qr.component = 'mod_quiz' AND qr.questionarea = 'slot' AND qr.itemid = slot.id
            INNER JOIN {question_bank_entries} qbe ON qbe.id = qr.questionbankentryid
            INNER JOIN {question_versions} qv ON qv.questionbankentryid = qbe.id
            INNER JOIN {question} q ON q.id = qv.questionid
        ) AND qtype IN('multichoice', 'essay', 'truefalse', 'shortanswer', 'numerical', 'match')";

$rs = $DB->get_recordset_sql($sql, null, 0, $numberofrecords);

try {
    $transaction = $DB->start_delegated_transaction();

    if (!$rs->valid()) {
        throw new Exception('Recordset not valid');
    }

    foreach ($rs as $question) {
        //echo $question->id . " " . $question->name . "\n";
        $versions = getVersions($question->id);

        if (count($versions) > 1) {
            echo $question->id . " has more than one version: qtd" . count($versions) . "\n";

            continue;
        }

        $version = current($versions);
        unset($versions);

        $references = getReferences($version->questionbankentryid);

        if ($references) {
            echo $question->id . " has references: qtd" . count($references) . "\n";

            continue;
        }

        deleteQuestionBankEntry($version->questionbankentryid);

        deleteQuestionVersion($question->id);

        if ($question->qtype != 'essay') {
            deleteAnswers($question->id);
        }

        deleteTypeOptions($question);

        deleteQuestion($question->id);
    }
    $rs->close();

    $DB->commit_delegated_transaction($transaction);
} catch (\Exception $e) {
    $transaction->rollback($e);

    throw $e;
}

$humantimenow = date('r', time());
mtrace("Task ended at: {$humantimenow}\n");

function getVersions($questionid) {
    global $DB;

    return $DB->get_records('question_versions', ['questionid' => $questionid]);
}

function getReferences($questionbankentryid) {
    global $DB;

    return $DB->get_records('question_references', ['questionbankentryid' => $questionbankentryid]);
}

function deleteQuestionBankEntry($questionbankentryid) {
    global $DB;

    $DB->delete_records('question_bank_entries', ['id' => $questionbankentryid]);
}

function deleteQuestionVersion($questionid) {
    global $DB;

    $DB->delete_records('question_versions', ['questionid' => $questionid]);
}

function deleteAnswers($questionid) {
    global $DB;

    $DB->delete_records('question_answers', ['question' => $questionid]);
}

function deleteTypeOptions($question) {
    global $DB;

    if ($question->qtype == 'multichoice') {
        $DB->delete_records('qtype_multichoice_options', ['questionid' => $question->id]);
    }

    if ($question->qtype == 'truefalse') {
        $DB->delete_records('question_truefalse', ['question' => $question->id]);
    }

    if ($question->qtype == 'essay') {
        $DB->delete_records('qtype_essay_options', ['questionid' => $question->id]);
    }

    if ($question->qtype == 'shortanswer') {
        $DB->delete_records('qtype_shortanswer_options', ['questionid' => $question->id]);
    }

    if ($question->qtype == 'numerical') {
        $DB->delete_records('question_numerical_options', ['question' => $question->id]);
        $DB->delete_records('question_numerical', ['question' => $question->id]);
    }

    if ($question->qtype == 'match') {
        $DB->delete_records('qtype_match_options', ['questionid' => $question->id]);
        $DB->delete_records('qtype_match_subquestions', ['questionid' => $question->id]);
    }
}

function deleteQuestion($questionid) {
    global $DB;

    $DB->delete_records('question', ['id' => $questionid]);
}