SQL: Looking for relation between questions and question categories

SQL: Looking for relation between questions and question categories

על ידי Ingo Marzolph בתאריך
מספר תגובות: 4

Hello,

want to build a SQL query to get a list of all questions in a question bank that is used in a course.

I understand that I could recieve this via the question category but I am struggeling to find the proper tables that I could join to get the relation between a question and the category.

All forum posts are referring to question.categories but this doesn´t seem to exist anymore (the post were pretty old).
Also the documentations here https://docs.moodle.org/dev/Question_database_structure and here https://docs.moodle.org/dev/Quiz_database_structure are telling me that the tables are linked via the category.

The relation must be moved to somewhere else but I can´t find it.

I would be really happy if someone give me a hint in the right direction.

Thanks,

Ingo

-----

Note: My complete goal is to have a query where I can also see if the question is hidden or not and if it is used in any of the quizzes inside a course. Maybe had a similar requirement in the past and can help me out.

ממוצע דרוגים:Useful (1)
בתגובה ל: Ingo Marzolph

Re: SQL: Looking for relation between questions and question categories

על ידי Marcus Green בתאריך
תמונה של Core developers תמונה של Particularly helpful Moodlers תמונה של Plugin developers תמונה של Testers
Do these help at all
https://examulator.com/er/3.8/quiz/relationships.html

For 4.0 where the question bank is different
https://examulator.com/er/output/tables/question_bank_entries.html
ממוצע דרוגים: -
בתגובה ל: Marcus Green

Re: SQL: Looking for relation between questions and question categories

על ידי Ingo Marzolph בתאריך
Hello Marcus,

thanks for the hint. I actually was working my way through this website. Great work! It is really helpfull.

But the only connection that I found from questions to the question_bank_entries tables was via the question_dataset tables.
Sadly those tables are completely empty in my MySQL table.

I can´t go over the quiz table, since I want to see also the questions in the question bank in a course that is not assigned to a quiz, so that we can find the unused questions.
ממוצע דרוגים: -
בתגובה ל: Ingo Marzolph

Re: SQL: Looking for relation between questions and question categories

על ידי Tim Hunt בתאריך
תמונה של Core developers תמונה של Documentation writers תמונה של Particularly helpful Moodlers תמונה של Peer reviewers תמונה של Plugin developers
The change in database structure is outlined in this forum post: https://moodle.org/mod/forum/discuss.php?d=417599#p1683186

The kind of joins you need can be found is various bits of code now. Here is one example https://github.com/studentquiz/moodle-mod_studentquiz/blob/5f2302fa6c73b9b512f747eca82b294092f4ca2a/locallib.php#L899
ממוצע דרוגים: -