protected function check_typical_question_attempts_query(qubaid_condition $qubaids, $expectedsql, $expectedparams)
{
$sql = "SELECT qa.id, qa.maxmark\n FROM {$qubaids->from_question_attempts('qa')}\n WHERE {$qubaids->where()} AND qa.slot = :slot";
$this->assertEqual($expectedsql, $sql);
$params = $qubaids->from_where_params();
$params['slot'] = 1;
$this->assertEqual($expectedparams, $params);
}
protected function check_typical_question_attempts_query(qubaid_condition $qubaids, $expectedsql, $expectedparams)
{
$sql = "SELECT qa.id, qa.maxmark\n FROM {$qubaids->from_question_attempts('qa')}\n WHERE {$qubaids->where()} AND qa.slot = :slot";
$params = $qubaids->from_where_params();
$params['slot'] = 1;
// NOTE: parameter names may change thanks to $DB->inorequaluniqueindex, normal comparison is very wrong!!
list($sql, $params) = $this->normalize_sql($sql, $params);
list($expectedsql, $expectedparams) = $this->normalize_sql($expectedsql, $expectedparams);
$this->assertEquals($expectedsql, $sql);
$this->assertEquals($expectedparams, $params);
}
/**
* Get a subquery that returns the latest step of every qa in some qubas.
* Currently, this is only used by the quiz reports. See
* {@link quiz_attempts_report_table::add_latest_state_join()}.
* @param string $alias alias to use for this inline-view.
* @param qubaid_condition $qubaids restriction on which question_usages we
* are interested in. This is important for performance.
* @return array with two elements, the SQL fragment and any params requried.
*/
public function question_attempt_latest_state_view($alias, qubaid_condition $qubaids)
{
return array("(\n SELECT {$alias}qa.id AS questionattemptid,\n {$alias}qa.questionusageid,\n {$alias}qa.slot,\n {$alias}qa.behaviour,\n {$alias}qa.questionid,\n {$alias}qa.variant,\n {$alias}qa.maxmark,\n {$alias}qa.minfraction,\n {$alias}qa.maxfraction,\n {$alias}qa.flagged,\n {$alias}qa.questionsummary,\n {$alias}qa.rightanswer,\n {$alias}qa.responsesummary,\n {$alias}qa.timemodified,\n {$alias}qas.id AS attemptstepid,\n {$alias}qas.sequencenumber,\n {$alias}qas.state,\n {$alias}qas.fraction,\n {$alias}qas.timecreated,\n {$alias}qas.userid\n\n FROM {$qubaids->from_question_attempts($alias . 'qa')}\n JOIN {question_attempt_steps} {$alias}qas ON\n {$alias}qas.id = {$this->latest_step_for_qa_subquery($alias . 'qa.id')}\n WHERE {$qubaids->where()}\n ) {$alias}", $qubaids->from_where_params());
}
/**
* Get the ids of all the questions in a list of categories, with the number
* of times they have already been used in a given set of usages.
*
* The result array is returned in order of increasing (count previous uses).
*
* @param array $categoryids an array question_category ids.
* @param qubaid_condition $qubaids which question_usages to count previous uses from.
* @param string $extraconditions extra conditions to AND with the rest of
* the where clause. Must use named parameters.
* @param array $extraparams any parameters used by $extraconditions.
* @return array questionid => count of number of previous uses.
*/
public function get_questions_from_categories_with_usage_counts($categoryids, qubaid_condition $qubaids, $extraconditions = '', $extraparams = array())
{
global $DB;
list($qcsql, $qcparams) = $DB->get_in_or_equal($categoryids, SQL_PARAMS_NAMED, 'qc');
if ($extraconditions) {
$extraconditions = ' AND (' . $extraconditions . ')';
}
return $DB->get_records_sql_menu("\n SELECT q.id, (SELECT COUNT(1)\n FROM " . $qubaids->from_question_attempts('qa') . "\n WHERE qa.questionid = q.id AND " . $qubaids->where() . "\n ) AS previous_attempts\n\n FROM {question} q\n\n WHERE q.category {$qcsql} {$extraconditions}\n\n ORDER BY previous_attempts\n ", $qubaids->from_where_params() + $qcparams + $extraparams);
}
/**
* Load a {@link question_attempt} from the database, including all its
* steps.
* @param int $questionid the question to load all the attempts fors.
* @param qubaid_condition $qubaids used to restrict which usages are included
* in the query. See {@link qubaid_condition}.
* @return array of question_attempts.
*/
public function load_attempts_at_question($questionid, qubaid_condition $qubaids)
{
global $DB;
$params = $qubaids->from_where_params();
$params['questionid'] = $questionid;
$records = $DB->get_recordset_sql("\nSELECT\n quba.contextid,\n quba.preferredbehaviour,\n qa.id AS questionattemptid,\n qa.questionusageid,\n qa.slot,\n qa.behaviour,\n qa.questionid,\n qa.variant,\n qa.maxmark,\n qa.minfraction,\n qa.flagged,\n qa.questionsummary,\n qa.rightanswer,\n qa.responsesummary,\n qa.timemodified,\n qas.id AS attemptstepid,\n qas.sequencenumber,\n qas.state,\n qas.fraction,\n qas.timecreated,\n qas.userid,\n qasd.name,\n qasd.value\n\nFROM {$qubaids->from_question_attempts('qa')}\nJOIN {question_usages} quba ON quba.id = qa.questionusageid\nLEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id\nLEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id\n\nWHERE\n {$qubaids->where()} AND\n qa.questionid = :questionid\n\nORDER BY\n quba.id,\n qa.id,\n qas.sequencenumber\n ", $params);
$questionattempts = array();
while ($records->valid()) {
$record = $records->current();
$questionattempts[$record->questionattemptid] = question_attempt::load_from_records($records, $record->questionattemptid, new question_usage_null_observer(), $record->preferredbehaviour);
}
$records->close();
return $questionattempts;
}
/**
* Get the number of times each variant has been used for each question in a list
* in a set of usages.
* @param array $questionids of question ids.
* @param qubaid_condition $qubaids ids of the usages to consider.
* @return array questionid => variant number => num uses.
*/
public function load_used_variants(array $questionids, qubaid_condition $qubaids)
{
list($test, $params) = $this->db->get_in_or_equal($questionids, SQL_PARAMS_NAMED, 'qid');
$recordset = $this->db->get_recordset_sql("\n SELECT qa.questionid, qa.variant, COUNT(1) AS usescount\n FROM " . $qubaids->from_question_attempts('qa') . "\n WHERE qa.questionid {$test}\n AND " . $qubaids->where() . "\n GROUP BY qa.questionid, qa.variant\n ORDER BY COUNT(1) ASC\n ", $params + $qubaids->from_where_params());
$usedvariants = array_combine($questionids, array_fill(0, count($questionids), array()));
foreach ($recordset as $row) {
$usedvariants[$row->questionid][$row->variant] = $row->usescount;
}
$recordset->close();
return $usedvariants;
}
/**
* Get all the STACK questions used in all the attempts at a quiz. (Note that
* Moodle random questions may be being used.)
* @param qubaid_condition $qubaids the attempts of interest.
* @return array of rows from the question table.
*/
protected function get_stack_questions_used_in_attempt(qubaid_condition $qubaids)
{
global $DB;
return $DB->get_records_sql("\n SELECT q.*\n FROM {question} q\n JOIN (\n SELECT qa.questionid, MIN(qa.slot) AS firstslot\n FROM {$qubaids->from_question_attempts('qa')}\n WHERE {$qubaids->where()}\n GROUP BY qa.questionid\n ) usedquestionids ON q.id = usedquestionids.questionid\n WHERE q.qtype = 'stack'\n ORDER BY usedquestionids.firstslot\n ", $qubaids->from_where_params());
}
