Find course where question from a category are used

Find course where question from a category are used

by Jürg Hoerner -
Number of replies: 2

If a question I like to delete is in a category. I don't know where. 


Is there a solution to see in which courses a question from a category is used, so I can find the quiz? 

My be I can see the course ID in the postgres database. 


My example: I can see in which category but not in which course ore courses. For deleting this old questions I need to know in which course they are used in a quiz.  

Report for question type 'Ordering'

ContextTotalVisibleHidden
Category: Fachbereich Wirtschaft und Recht404
Total404

If the questions are in a course, I will see a link to the course. 

Thanks for your answers. 

Jürg

Average of ratings: -
In reply to Jürg Hoerner

Re: Find course where question from a category are used

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

This is close

SELECT
    c.shortname AS Course,
    quiz.name AS Quiz

FROM {course} c
JOIN {quiz} quiz ON quiz.course = c.id
JOIN {quiz_slots} slot ON slot.quizid = quiz.id
JOIN {question} q ON q.id = slot.questionid
WHERE q.id = 12345
   OR q.qtype = 'random' AND EXISTS (
            SELECT 1
            FROM {question_categories} qc
            JOIN {question} rq ON rq.category = qc.id
            WHERE qc.id = q.category AND rq.id = 12345
        )
    )
)
ORDER Course, Quiz

It will find all the Quizzes where the question with id 12345 is used, whether directly, or via a random question. (Except that, it won't work in the cases where you have a random question picking from a particular category and any subcategories.)

Note, you need to replace the table names in {} with whatever your databse prefix is. e.g. {quiz} -> mdl_quiz

Average of ratings: Useful (1)
In reply to Tim Hunt

Re: Find course where question from a category are used

by Jürg Hoerner -

Thank you very, very much. This was a great help: 

With this code it worked fine I added ORDER BY  and deleted two )  : 

SELECT

    c.shortname AS Course,

    quiz.name AS Quiz

FROM mdl_course c

JOIN mdl_quiz quiz ON quiz.course = c.id

JOIN mdl_quiz_slots slot ON slot.quizid = quiz.id

JOIN mdl_question q ON q.id = slot.questionid

WHERE q.id = 681

   OR q.qtype = 'random' AND EXISTS (

            SELECT 1

            FROM mdl_question_categories qc

            JOIN mdl_question rq ON rq.category = qc.id

            WHERE qc.id = q.category AND rq.id = 681

        )

ORDER BY Course, Quiz

Average of ratings: -