Skip to main content

Question Bank - How do I clean this up?

Comments

18 comments

  • Gabrielle Rumley - Smith

    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
  • Jo James

    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 Rodgers

    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 CourseName

    FROM 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.id 

    WHERE mdl_course.id=5208 AND q.qtype IS NOT NULL

    -------------
    Change the above line to whatever course id you are wanting.  

    2
  • Jo James

    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
  • Ed Ladenburger

    Tracking this issue -- we require similar assistance in cleaning up question banks - -in particular, the growing number of unnecessary "versions" for each question. 

    0
  • Jason Rodgers

    RUN as a DEFERRED Query -- This gives a count of the number of question in each class
    ---------------

    Select
    MC.instanceid as CourseID,
    count(*) as QuestionCount

    FROM 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.instanceid 

    GROUP BY mdl_course.id

    1
  • Jo James

    I'm trying it now.

     

    0
  • Ed Ladenburger

    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
  • Gretchen Benton

    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
  • Jason Rodgers

    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 QuestionCount

    FROM 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.instanceid 

    GROUP BY mdl_course.id

    1
  • Jo James

    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
  • Ed Ladenburger

    Thank you, Jason -- I am running the revised query now...

    0
  • Gretchen Benton

    Ran the revised query, still have a the same blank cell on the CourseID collumn for that one over 100k questions. 

    0
  • Jo James

    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 QuestionCount

    FROM 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.instanceid 

    GROUP BY mdl_course.id

     

    0
  • Janelle Cleaves

    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
  • Jason Rodgers

    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 QuestionCount

    FROM 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.instanceid 

    Where MC.contextlevel = 50
    GROUP BY mdl_course.id

    0
  • Janelle Cleaves

    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
  • Ed Ladenburger

    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.