Question Bank - How do I clean this up?
Our Question Bank has descended into disarray, presenting a significant challenge in seamless navigation. It has become evident that its current disorder is impeding our ability to fully leverage Moodle for educational endeavors.
Yet, I find myself at a loss as to where to begin and how best to approach this endeavor. Thus, I am reaching out to you all, seeking your insights and assistance in orchestrating the transformation of our disorganized Question Bank into a meticulously organized resource. Your support in this endeavor would be invaluable, and I am eager to collaborate with you in revitalizing our Moodle platform.
-
Might give you a starter on looking at the questionbank,
-------------
SELECT
q.qtype,
#q.questiontext AS CODE,
QA.answer,
q.name AS ItemName,
QC.name AS CategoryName,
mdl_quiz.name AS QuizName,
mdl_course.fullname AS CourseNameFROM mdl_quiz_slots slot
LEFT JOIN mdl_question_references qr ON qr.itemid = slot.id
LEFT JOIN mdl_question_bank_entries qbe ON qbe.id = qr.questionbankentryid
LEFT join mdl_question_categories QC ON qbe.questioncategoryid=QC.id
LEFT JOIN mdl_question_versions qv ON qv.questionbankentryid = qbe.id
LEFT JOIN mdl_question q ON q.id = qv.questionid
LEFT JOIN mdl_question_answers QA ON QA.question = q.id
LEFT JOIN mdl_quiz ON slot.quizid = mdl_quiz.id
LEFT JOIN mdl_course ON mdl_quiz.course=mdl_course.idWHERE mdl_course.id=5208 AND q.qtype IS NOT NULL
-------------
Change the above line to whatever course id you are wanting.3 -
Agreed! Our question bank is in complete disarry. There are numerous categories duplicated. Questions and categories from different courses find there way into an unrelated course. Instructors who created the question can now no longer locate the question or edit it if found. The question bank issues are affecting course import, backup, and restore features.
2 -
Agreed, again! I have been working with instructors on an individual basis and the clean up is labor intensive as well as frustrating.
I know that there are courses that I have not yet identified that have a similar problem In addition to a way to do clean up is there a way I can identify the size of quiz banks for individual courses via some type of reporting mechanism?
1 -
Jason,
Is there a way to modify the query to report out site-wide question bank size for all courses in a single report? I would like to narrow down to specific courses (e.g. courses that have large question banks) that I need to target first for deep cleaning.
Thanks so much for sharing.
1 -
RUN as a DEFERRED Query -- This gives a count of the number of question in each class
---------------
Select
MC.instanceid as CourseID,
count(*) as QuestionCountFROM mdl_question q
LEFT JOIN mdl_question_versions qv ON qv.questionid = q.id
LEFT JOIN mdl_question_bank_entries qbe ON qbe.id= qv.questionbankentryid
LEFT join mdl_question_categories QC ON QC.id = qbe.questioncategoryid
LEFT JOIN mdl_context MC ON MC.id = QC.contextid
LEFT JOIN mdl_course ON mdl_course.id=MC.instanceidGROUP BY mdl_course.id
1 -
The 878 might have been a category.
Change the the query to
Notice the second line is the only thing that changed.
----Select
mdl_course.id as CourseID,
count(*) as QuestionCountFROM mdl_question q
LEFT JOIN mdl_question_versions qv ON qv.questionid = q.id
LEFT JOIN mdl_question_bank_entries qbe ON qbe.id= qv.questionbankentryid
LEFT join mdl_question_categories QC ON QC.id = qbe.questioncategoryid
LEFT JOIN mdl_context MC ON MC.id = QC.contextid
LEFT JOIN mdl_course ON mdl_course.id=MC.instanceidGROUP BY mdl_course.id
1 -
The query provided the count per course and provided the courseid number. Excellent news. I checked a couple of the high counts and found that they did, indeed, have lots of questions in the course.
I did have one record reported that had 29,178 questions but there was no courseid value provided. Do you think this value are questions that are stored at the category and/or system level question banks?
1 -
Tracking this issue -- we require similar assistance in cleaning up question banks - -in particular, the growing number of unnecessary "versions" for each question.
0 -
I'm trying it now.
0 -
Hello Jason -- and others ...
I ran the query above and am now troubled by the fact that the number one question bank offender (by a factor of 10, query says it contains 261656 questions) is a "course" I cannot access...
0 -
Hello,
This is much needed at our school as well! I ran the report and also had an odd issue with the largest question bank listed. I have one line showing 118,537 questions but there is no courseid listed. See screenshot. Could these exist at the category level? If so is there a way to add a line to the SQL code to display the category name or category ID so that we can investigate this issue more.
0 -
Thank you, Jason -- I am running the revised query now...
0 -
Ran the revised query, still have a the same blank cell on the CourseID collumn for that one over 100k questions.
0 -
Jason, the query you provided earlier was almost exactly what I need. Is it possible to change the code to provide the course short name in addition to the course id? Here's the code you provided.
Select
MC.instanceid as CourseID,
count(*) as QuestionCountFROM mdl_question q
LEFT JOIN mdl_question_versions qv ON qv.questionid = q.id
LEFT JOIN mdl_question_bank_entries qbe ON qbe.id= qv.questionbankentryid
LEFT join mdl_question_categories QC ON QC.id = qbe.questioncategoryid
LEFT JOIN mdl_context MC ON MC.id = QC.contextid
LEFT JOIN mdl_course ON mdl_course.id=MC.instanceidGROUP BY mdl_course.id
0 -
Hey all,
I have the same issue with the blank CourseID cell for one course. I know nothing about SQL, but could we make another report that can search for a class with that specific number of questions in it? Wouldn't fix the issue, but you might be able to get to the same point.
0 -
Adding in a context level check of 50 (means it is looking at a course) will help
----------------
Select
MC.instanceid as CourseID,
mdl_course.fullname,
count(*) as QuestionCountFROM mdl_question q
LEFT JOIN mdl_question_versions qv ON qv.questionid = q.id
LEFT JOIN mdl_question_bank_entries qbe ON qbe.id= qv.questionbankentryid
LEFT join mdl_question_categories QC ON QC.id = qbe.questioncategoryid
LEFT JOIN mdl_context MC ON MC.id = QC.contextid
LEFT JOIN mdl_course ON mdl_course.id=MC.instanceidWhere MC.contextlevel = 50
GROUP BY mdl_course.id0 -
This seemed to work well for me, though I can't find the old bank that had no course ID listed. I don't know if the number of questions in that bank just changed since I ran this, or if it wasn't retrieved. Regardless, this is very helpful!
0 -
Again -- thank you Jason! Like Janelle -- the begging question -- where was that massive question count number coming from (if not a course)? A category? Someplace in the data_table to which we do or don't have access?
0
Please sign in to leave a comment.
Comments
18 comments