Every Question attempted by a student

Every Question attempted by a student

by Arvind Murali -
Number of replies: 7

I'd like to create a custom SQL query to be run through the configurable Reports which gives me for a given course id, all questions that were attempted by all users within the last day and also show the questions, whether they got it right or not cumulatively from multiple activity modules. I have about 30 questions in a category and there are different types of activities that use these same questions - for eg. a quiz, a couple of games etc. I want teachers to be able to pull a report that lists the number of questions each of their students worked on last night. I say worked on because, even if the student did not "finish an attempt" in a quiz, I want to be able to report that they tried 8 questions and got 6 right in this quiz and then did 5 questions by playing the quizverse quiz for example. 

The table at the end of it I think would look like this:

Table


I have been confused about how to go about this. Activity logs seem to be right place to start but it doesn't give me all the info.  Any pointers?

Average of ratings: -
In reply to Arvind Murali

Re: Every Question attempted by a student

by Randy Thornton -
Picture of Documentation writers
Arvind,

Have you looked at the table mdl_question_attempt_steps and in particular at the 'state' column there? That may help you with seeing items in progress as users do the activity that uses the questions, but not yet submitted to the activity. It works for the standard quiz. Whether it works for other activities depends on whether those are well coded to keep the state of each question as they go along.

There's a list of all these possible states (with some comments on what they do) in the code in the /question/engine/states.php file.
Average of ratings: -
In reply to Randy Thornton

Re: Every Question attempted by a student

by Arvind Murali -
Thank you for the suggestion Randy. I took a look at the mdl_question_attempt_steps table. This gives me the info I was looking for to begin with. However I'm confused about the way to go about this. The attempt_steps table tracks userid and states and time created. I wanted to get only the attempts of questions within a particular course. How can I go about that? I went down the path of getting the context id and all that but it was not straightforward to even figure out which context id was within a given course. The only roundabout way I figured of understanding this would be to do this: 
         Get attempt_id  --> Get usage id from attempts table --> Get contextid from usage table --> Get path from context table --> Get course path to see if the context id belongs to a certain course. I think there might be easier ways to do this. 

Any pointers?
Average of ratings: -
In reply to Arvind Murali

Re: Every Question attempted by a student

by Randy Thornton -
Picture of Documentation writers
The document Tim linked to will be very useful. It's a great place to start.

How to limit this to a course? The missing link to get to the quiz itself. I would avoid going through the context id in this case - for reasons you already found out ;). As Tim's code shows, you can get to the quiz_attempts table via the question_usages table.

Get question_usage id from question_attempts table
Get quiz_attempts id from the question_usages.uniqueid

(as shown in the code on that linked page), then you need to:

Get quiz id from the quiz_attempts table
Get the course id from the quiz table

Put the course id in your WHERE statement.
Average of ratings: -
In reply to Randy Thornton

Re: Every Question attempted by a student

by Arvind Murali -
Makes sense. Thank you! Will work on getting this to display! By any chance do you know if a block can be displayed in the center of the course page instead of right on a boost theme? I'd like to have a block displaying this table in a course page but the side is very small to display this.

Thanks.
Average of ratings: -
In reply to Arvind Murali

Re: Every Question attempted by a student

by Arvind Murali -
Randy, just realized something. The quiz attempts unique id naturally only tracks quizzes that the user took. I'm tying all forms of modules that engaged with questions through the question_attempts table. So I'd need to figure out a way to restrict to course with info from the question attempt table which is what led me to the contextid. I am writing a custom block for course context, so an alternate idea I had was to get the current courseid, pull all the modules from within the course through some method ( I'm yet to look it up). Then iterate on all the question attempts that had the context id of that using an sql query. Thoughts?
Average of ratings: -
In reply to Arvind Murali

Re: Every Question attempted by a student

by Randy Thornton -
Picture of Documentation writers
Arvind,

I think it will depend on the way each of those other modules connects to the question_attempts table. If a module uses the same method as the quiz and so question_usages.uniqueid leads you back to the module, then you can use the same method as with the quiz.

However, if the module use other methods, such as skipping the question_usages table entirely, then, yes, as you say, you may need to go through the contextid.

Modules may do this in ways very different from the way the quiz does it. So, how many ways are there for a module to use the question_usages and question_attempts tables?

I can't speak to the API here, since I don't know how it works, but if I were doing such a report in just SQL, I would first write a separate query for each module to get the results. Then I would understand each method. Then I would put them together into one report.
Average of ratings: -