MDL-29520 - mysql multitable delete, outer join some tables
[moodle.git] / question / engine / datalib.php
CommitLineData
d1b7e03d 1<?php
d1b7e03d
TH
2// This file is part of Moodle - http://moodle.org/
3//
4// Moodle is free software: you can redistribute it and/or modify
5// it under the terms of the GNU General Public License as published by
6// the Free Software Foundation, either version 3 of the License, or
7// (at your option) any later version.
8//
9// Moodle is distributed in the hope that it will be useful,
10// but WITHOUT ANY WARRANTY; without even the implied warranty of
11// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12// GNU General Public License for more details.
13//
14// You should have received a copy of the GNU General Public License
15// along with Moodle. If not, see <http://www.gnu.org/licenses/>.
16
d1b7e03d 17/**
5e8a85aa 18 * Code for loading and saving question attempts to and from the database.
d1b7e03d 19 *
017bc1d9 20 * @package moodlecore
d1b7e03d 21 * @subpackage questionengine
017bc1d9
TH
22 * @copyright 2009 The Open University
23 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
24 */
25
26
a17b297d
TH
27defined('MOODLE_INTERNAL') || die();
28
29
d1b7e03d
TH
30/**
31 * This class controls the loading and saving of question engine data to and from
32 * the database.
33 *
017bc1d9
TH
34 * @copyright 2009 The Open University
35 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
36 */
37class question_engine_data_mapper {
06f8ed54
TH
38 /**
39 * @var moodle_database normally points to global $DB, but I prefer not to
40 * use globals if I can help it.
41 */
42 protected $db;
43
44 /**
45 * @param moodle_database $db a database connectoin. Defaults to global $DB.
46 */
47 public function __construct($db = null) {
48 if (is_null($db)) {
49 global $DB;
06f8ed54
TH
50 $this->db = $DB;
51 } else {
52 $this->db = $db;
53 }
54 }
55
d1b7e03d
TH
56 /**
57 * Store an entire {@link question_usage_by_activity} in the database,
58 * including all the question_attempts that comprise it.
59 * @param question_usage_by_activity $quba the usage to store.
60 */
61 public function insert_questions_usage_by_activity(question_usage_by_activity $quba) {
0ff4bd08 62 $record = new stdClass();
d1b7e03d 63 $record->contextid = $quba->get_owning_context()->id;
56b0df7e
TH
64 $record->component = $quba->get_owning_component();
65 $record->preferredbehaviour = $quba->get_preferred_behaviour();
d1b7e03d 66
06f8ed54 67 $newid = $this->db->insert_record('question_usages', $record);
d1b7e03d
TH
68 $quba->set_id_from_database($newid);
69
70 foreach ($quba->get_attempt_iterator() as $qa) {
217f9a61 71 $this->insert_question_attempt($qa, $quba->get_owning_context());
d1b7e03d
TH
72 }
73 }
74
75 /**
76 * Store an entire {@link question_attempt} in the database,
77 * including all the question_attempt_steps that comprise it.
78 * @param question_attempt $qa the question attempt to store.
217f9a61 79 * @param object $context the context of the owning question_usage_by_activity.
d1b7e03d 80 */
217f9a61 81 public function insert_question_attempt(question_attempt $qa, $context) {
0ff4bd08 82 $record = new stdClass();
d1b7e03d
TH
83 $record->questionusageid = $qa->get_usage_id();
84 $record->slot = $qa->get_slot();
56b0df7e 85 $record->behaviour = $qa->get_behaviour_name();
d1b7e03d 86 $record->questionid = $qa->get_question()->id;
1da821bb 87 $record->variant = $qa->get_variant();
d1b7e03d
TH
88 $record->maxmark = $qa->get_max_mark();
89 $record->minfraction = $qa->get_min_fraction();
90 $record->flagged = $qa->is_flagged();
56b0df7e
TH
91 $record->questionsummary = $qa->get_question_summary();
92 $record->rightanswer = $qa->get_right_answer_summary();
93 $record->responsesummary = $qa->get_response_summary();
d1b7e03d 94 $record->timemodified = time();
06f8ed54 95 $record->id = $this->db->insert_record('question_attempts', $record);
d1b7e03d
TH
96
97 foreach ($qa->get_step_iterator() as $seq => $step) {
217f9a61 98 $this->insert_question_attempt_step($step, $record->id, $seq, $context);
d1b7e03d
TH
99 }
100 }
101
102 /**
103 * Store a {@link question_attempt_step} in the database.
104 * @param question_attempt_step $qa the step to store.
217f9a61
TH
105 * @param int $questionattemptid the question attept id this step belongs to.
106 * @param int $seq the sequence number of this stop.
107 * @param object $context the context of the owning question_usage_by_activity.
d1b7e03d
TH
108 */
109 public function insert_question_attempt_step(question_attempt_step $step,
217f9a61 110 $questionattemptid, $seq, $context) {
0ff4bd08 111 $record = new stdClass();
d1b7e03d
TH
112 $record->questionattemptid = $questionattemptid;
113 $record->sequencenumber = $seq;
56b0df7e 114 $record->state = '' . $step->get_state();
d1b7e03d
TH
115 $record->fraction = $step->get_fraction();
116 $record->timecreated = $step->get_timecreated();
117 $record->userid = $step->get_user_id();
118
06f8ed54 119 $record->id = $this->db->insert_record('question_attempt_steps', $record);
d1b7e03d
TH
120
121 foreach ($step->get_all_data() as $name => $value) {
48d9c17d
TH
122 if ($value instanceof question_file_saver) {
123 $value->save_files($record->id, $context);
124 }
125
0ff4bd08 126 $data = new stdClass();
d1b7e03d 127 $data->attemptstepid = $record->id;
56b0df7e
TH
128 $data->name = $name;
129 $data->value = $value;
06f8ed54 130 $this->db->insert_record('question_attempt_step_data', $data, false);
d1b7e03d
TH
131 }
132 }
133
134 /**
135 * Load a {@link question_attempt_step} from the database.
f7970e3c 136 * @param int $stepid the id of the step to load.
d1b7e03d
TH
137 * @param question_attempt_step the step that was loaded.
138 */
139 public function load_question_attempt_step($stepid) {
35d5f1c2 140 $records = $this->db->get_recordset_sql("
d1b7e03d 141SELECT
d1b7e03d
TH
142 qas.id AS attemptstepid,
143 qas.questionattemptid,
144 qas.sequencenumber,
145 qas.state,
146 qas.fraction,
147 qas.timecreated,
148 qas.userid,
149 qasd.name,
150 qasd.value
151
06f8ed54
TH
152FROM {question_attempt_steps} qas
153LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
d1b7e03d
TH
154
155WHERE
06f8ed54
TH
156 qas.id = :stepid
157 ", array('stepid' => $stepid));
d1b7e03d 158
35d5f1c2 159 if (!$records->valid()) {
88f0eb15 160 throw new coding_exception('Failed to load question_attempt_step ' . $stepid);
d1b7e03d
TH
161 }
162
35d5f1c2
TH
163 $step = question_attempt_step::load_from_records($records, $stepid);
164 $records->close();
165
166 return $step;
d1b7e03d
TH
167 }
168
169 /**
170 * Load a {@link question_attempt} from the database, including all its
171 * steps.
f7970e3c 172 * @param int $questionattemptid the id of the question attempt to load.
d1b7e03d
TH
173 * @param question_attempt the question attempt that was loaded.
174 */
175 public function load_question_attempt($questionattemptid) {
35d5f1c2 176 $records = $this->db->get_recordset_sql("
d1b7e03d 177SELECT
7a719748 178 quba.contextid,
d1b7e03d
TH
179 quba.preferredbehaviour,
180 qa.id AS questionattemptid,
181 qa.questionusageid,
182 qa.slot,
183 qa.behaviour,
184 qa.questionid,
1da821bb 185 qa.variant,
d1b7e03d
TH
186 qa.maxmark,
187 qa.minfraction,
188 qa.flagged,
189 qa.questionsummary,
190 qa.rightanswer,
191 qa.responsesummary,
192 qa.timemodified,
193 qas.id AS attemptstepid,
194 qas.sequencenumber,
195 qas.state,
196 qas.fraction,
197 qas.timecreated,
198 qas.userid,
199 qasd.name,
200 qasd.value
201
06f8ed54
TH
202FROM {question_attempts qa
203JOIN {question_usages} quba ON quba.id = qa.questionusageid
204LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
205LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
d1b7e03d
TH
206
207WHERE
06f8ed54 208 qa.id = :questionattemptid
d1b7e03d
TH
209
210ORDER BY
211 qas.sequencenumber
06f8ed54 212 ", array('questionattemptid' => $questionattemptid));
d1b7e03d 213
35d5f1c2 214 if (!$records->valid()) {
88f0eb15 215 throw new coding_exception('Failed to load question_attempt ' . $questionattemptid);
d1b7e03d
TH
216 }
217
218 $record = current($records);
35d5f1c2 219 $qa = question_attempt::load_from_records($records, $questionattemptid,
d1b7e03d 220 new question_usage_null_observer(), $record->preferredbehaviour);
35d5f1c2
TH
221 $records->close();
222
223 return $qa;
d1b7e03d
TH
224 }
225
226 /**
227 * Load a {@link question_usage_by_activity} from the database, including
228 * all its {@link question_attempt}s and all their steps.
f7970e3c 229 * @param int $qubaid the id of the usage to load.
d1b7e03d
TH
230 * @param question_usage_by_activity the usage that was loaded.
231 */
232 public function load_questions_usage_by_activity($qubaid) {
35d5f1c2 233 $records = $this->db->get_recordset_sql("
d1b7e03d 234SELECT
d1b7e03d
TH
235 quba.id AS qubaid,
236 quba.contextid,
237 quba.component,
238 quba.preferredbehaviour,
239 qa.id AS questionattemptid,
240 qa.questionusageid,
241 qa.slot,
242 qa.behaviour,
243 qa.questionid,
1da821bb 244 qa.variant,
d1b7e03d
TH
245 qa.maxmark,
246 qa.minfraction,
247 qa.flagged,
248 qa.questionsummary,
249 qa.rightanswer,
250 qa.responsesummary,
251 qa.timemodified,
252 qas.id AS attemptstepid,
253 qas.sequencenumber,
254 qas.state,
255 qas.fraction,
256 qas.timecreated,
257 qas.userid,
258 qasd.name,
259 qasd.value
260
06f8ed54
TH
261FROM {question_usages} quba
262LEFT JOIN {question_attempts} qa ON qa.questionusageid = quba.id
263LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
264LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
d1b7e03d
TH
265
266WHERE
06f8ed54 267 quba.id = :qubaid
d1b7e03d
TH
268
269ORDER BY
270 qa.slot,
271 qas.sequencenumber
c76145d3 272 ", array('qubaid' => $qubaid));
d1b7e03d 273
35d5f1c2 274 if (!$records->valid()) {
88f0eb15 275 throw new coding_exception('Failed to load questions_usage_by_activity ' . $qubaid);
d1b7e03d
TH
276 }
277
35d5f1c2
TH
278 $quba = question_usage_by_activity::load_from_records($records, $qubaid);
279 $records->close();
280
281 return $quba;
d1b7e03d
TH
282 }
283
284 /**
285 * Load information about the latest state of each question from the database.
286 *
287 * @param qubaid_condition $qubaids used to restrict which usages are included
288 * in the query. See {@link qubaid_condition}.
289 * @param array $slots A list of slots for the questions you want to konw about.
290 * @return array of records. See the SQL in this function to see the fields available.
291 */
292 public function load_questions_usages_latest_steps(qubaid_condition $qubaids, $slots) {
a2ac2349 293 list($slottest, $params) = $this->db->get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot');
d1b7e03d 294
9b40c540 295 $records = $this->db->get_records_sql("
d1b7e03d
TH
296SELECT
297 qas.id,
298 qa.id AS questionattemptid,
299 qa.questionusageid,
300 qa.slot,
301 qa.behaviour,
302 qa.questionid,
1da821bb 303 qa.variant,
d1b7e03d
TH
304 qa.maxmark,
305 qa.minfraction,
306 qa.flagged,
307 qa.questionsummary,
308 qa.rightanswer,
309 qa.responsesummary,
310 qa.timemodified,
311 qas.id AS attemptstepid,
312 qas.sequencenumber,
313 qas.state,
314 qas.fraction,
315 qas.timecreated,
316 qas.userid
317
318FROM {$qubaids->from_question_attempts('qa')}
06f8ed54 319JOIN {question_attempt_steps} qas ON
d1b7e03d
TH
320 qas.id = {$this->latest_step_for_qa_subquery()}
321
322WHERE
323 {$qubaids->where()} AND
324 qa.slot $slottest
06f8ed54 325 ", $params + $qubaids->from_where_params());
d1b7e03d
TH
326
327 return $records;
328 }
329
330 /**
5e8a85aa
TH
331 * Load summary information about the state of each question in a group of
332 * attempts. This is used, for example, by the quiz manual grading report,
333 * to show how many attempts at each question need to be graded.
d1b7e03d
TH
334 *
335 * @param qubaid_condition $qubaids used to restrict which usages are included
336 * in the query. See {@link qubaid_condition}.
337 * @param array $slots A list of slots for the questions you want to konw about.
338 * @return array The array keys are slot,qestionid. The values are objects with
339 * fields $slot, $questionid, $inprogress, $name, $needsgrading, $autograded,
340 * $manuallygraded and $all.
341 */
9c197f44
TH
342 public function load_questions_usages_question_state_summary(
343 qubaid_condition $qubaids, $slots) {
a2ac2349 344 list($slottest, $params) = $this->db->get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot');
d1b7e03d 345
06f8ed54 346 $rs = $this->db->get_recordset_sql("
d1b7e03d
TH
347SELECT
348 qa.slot,
349 qa.questionid,
350 q.name,
351 CASE qas.state
352 {$this->full_states_to_summary_state_sql()}
353 END AS summarystate,
354 COUNT(1) AS numattempts
355
356FROM {$qubaids->from_question_attempts('qa')}
06f8ed54 357JOIN {question_attempt_steps} qas ON
d1b7e03d 358 qas.id = {$this->latest_step_for_qa_subquery()}
06f8ed54 359JOIN {question} q ON q.id = qa.questionid
d1b7e03d
TH
360
361WHERE
362 {$qubaids->where()} AND
363 qa.slot $slottest
364
365GROUP BY
366 qa.slot,
367 qa.questionid,
368 q.name,
369 q.id,
370 summarystate
371
f9b0500f 372ORDER BY
d1b7e03d
TH
373 qa.slot,
374 qa.questionid,
375 q.name,
376 q.id
06f8ed54 377 ", $params + $qubaids->from_where_params());
d1b7e03d 378
d1b7e03d 379 $results = array();
cf3b6568 380 foreach ($rs as $row) {
d1b7e03d
TH
381 $index = $row->slot . ',' . $row->questionid;
382
383 if (!array_key_exists($index, $results)) {
0ff4bd08 384 $res = new stdClass();
d1b7e03d
TH
385 $res->slot = $row->slot;
386 $res->questionid = $row->questionid;
387 $res->name = $row->name;
388 $res->inprogress = 0;
389 $res->needsgrading = 0;
390 $res->autograded = 0;
391 $res->manuallygraded = 0;
392 $res->all = 0;
393 $results[$index] = $res;
394 }
395
396 $results[$index]->{$row->summarystate} = $row->numattempts;
397 $results[$index]->all += $row->numattempts;
398 }
cf3b6568 399 $rs->close();
d1b7e03d
TH
400
401 return $results;
402 }
403
404 /**
405 * Get a list of usage ids where the question with slot $slot, and optionally
406 * also with question id $questionid, is in summary state $summarystate. Also
407 * return the total count of such states.
408 *
409 * Only a subset of the ids can be returned by using $orderby, $limitfrom and
410 * $limitnum. A special value 'random' can be passed as $orderby, in which case
411 * $limitfrom is ignored.
412 *
413 * @param qubaid_condition $qubaids used to restrict which usages are included
414 * in the query. See {@link qubaid_condition}.
f7970e3c
TH
415 * @param int $slot The slot for the questions you want to konw about.
416 * @param int $questionid (optional) Only return attempts that were of this specific question.
d1b7e03d
TH
417 * @param string $summarystate the summary state of interest, or 'all'.
418 * @param string $orderby the column to order by.
cf3b6568 419 * @param array $params any params required by any of the SQL fragments.
f7970e3c 420 * @param int $limitfrom implements paging of the results.
d1b7e03d 421 * Ignored if $orderby = random or $limitnum is null.
f7970e3c 422 * @param int $limitnum implements paging of the results. null = all.
d1b7e03d
TH
423 * @return array with two elements, an array of usage ids, and a count of the total number.
424 */
425 public function load_questions_usages_where_question_in_state(
426 qubaid_condition $qubaids, $summarystate, $slot, $questionid = null,
cf3b6568 427 $orderby = 'random', $params, $limitfrom = 0, $limitnum = null) {
d1b7e03d
TH
428
429 $extrawhere = '';
430 if ($questionid) {
cf3b6568
TH
431 $extrawhere .= ' AND qa.questionid = :questionid';
432 $params['questionid'] = $questionid;
d1b7e03d
TH
433 }
434 if ($summarystate != 'all') {
cf3b6568 435 list($test, $sparams) = $this->in_summary_state_test($summarystate);
d1b7e03d 436 $extrawhere .= ' AND qas.state ' . $test;
cf3b6568 437 $params += $sparams;
d1b7e03d
TH
438 }
439
440 if ($orderby == 'random') {
441 $sqlorderby = '';
442 } else if ($orderby) {
443 $sqlorderby = 'ORDER BY ' . $orderby;
444 } else {
445 $sqlorderby = '';
446 }
447
448 // We always want the total count, as well as the partcular list of ids,
449 // based on the paging and sort order. Becuase the list of ids is never
450 // going to be too rediculously long. My worst-case scenario is
451 // 10,000 students in the coures, each doing 5 quiz attempts. That
452 // is a 50,000 element int => int array, which PHP seems to use 5MB
453 // memeory to store on a 64 bit server.
cf3b6568
TH
454 $params += $qubaids->from_where_params();
455 $params['slot'] = $slot;
456 $qubaids = $this->db->get_records_sql_menu("
d1b7e03d
TH
457SELECT
458 qa.questionusageid,
459 1
460
461FROM {$qubaids->from_question_attempts('qa')}
cf3b6568 462JOIN {question_attempt_steps} qas ON
d1b7e03d 463 qas.id = {$this->latest_step_for_qa_subquery()}
cf3b6568 464JOIN {question} q ON q.id = qa.questionid
d1b7e03d
TH
465
466WHERE
467 {$qubaids->where()} AND
cf3b6568 468 qa.slot = :slot
d1b7e03d
TH
469 $extrawhere
470
471$sqlorderby
cf3b6568 472 ", $params);
d1b7e03d
TH
473
474 $qubaids = array_keys($qubaids);
475 $count = count($qubaids);
476
477 if ($orderby == 'random') {
478 shuffle($qubaids);
479 $limitfrom = 0;
480 }
481
482 if (!is_null($limitnum)) {
483 $qubaids = array_slice($qubaids, $limitfrom, $limitnum);
484 }
485
486 return array($qubaids, $count);
487 }
488
489 /**
490 * Load a {@link question_usage_by_activity} from the database, including
491 * all its {@link question_attempt}s and all their steps.
492 * @param qubaid_condition $qubaids used to restrict which usages are included
493 * in the query. See {@link qubaid_condition}.
494 * @param array $slots if null, load info for all quesitions, otherwise only
495 * load the averages for the specified questions.
496 */
497 public function load_average_marks(qubaid_condition $qubaids, $slots = null) {
d1b7e03d 498 if (!empty($slots)) {
9c197f44
TH
499 list($slottest, $slotsparams) = $this->db->get_in_or_equal(
500 $slots, SQL_PARAMS_NAMED, 'slot');
d1b7e03d
TH
501 $slotwhere = " AND qa.slot $slottest";
502 } else {
503 $slotwhere = '';
e24ee794 504 $params = array();
d1b7e03d
TH
505 }
506
e24ee794 507 list($statetest, $stateparams) = $this->db->get_in_or_equal(array(
d1b7e03d
TH
508 question_state::$gaveup,
509 question_state::$gradedwrong,
510 question_state::$gradedpartial,
511 question_state::$gradedright,
512 question_state::$mangaveup,
513 question_state::$mangrwrong,
514 question_state::$mangrpartial,
a2ac2349 515 question_state::$mangrright), SQL_PARAMS_NAMED, 'st');
d1b7e03d 516
e24ee794 517 return $this->db->get_records_sql("
d1b7e03d
TH
518SELECT
519 qa.slot,
520 AVG(COALESCE(qas.fraction, 0)) AS averagefraction,
521 COUNT(1) AS numaveraged
522
523FROM {$qubaids->from_question_attempts('qa')}
2a3bdbf9 524JOIN {question_attempt_steps} qas ON
d1b7e03d
TH
525 qas.id = {$this->latest_step_for_qa_subquery()}
526
527WHERE
528 {$qubaids->where()}
529 $slotwhere
530 AND qas.state $statetest
531
532GROUP BY qa.slot
533
534ORDER BY qa.slot
e24ee794 535 ", $slotsparams + $stateparams + $qubaids->from_where_params());
d1b7e03d
TH
536 }
537
538 /**
539 * Load a {@link question_attempt} from the database, including all its
540 * steps.
f7970e3c 541 * @param int $questionid the question to load all the attempts fors.
d1b7e03d
TH
542 * @param qubaid_condition $qubaids used to restrict which usages are included
543 * in the query. See {@link qubaid_condition}.
544 * @return array of question_attempts.
545 */
546 public function load_attempts_at_question($questionid, qubaid_condition $qubaids) {
9b40c540 547 global $DB;
04853f27
TH
548
549 $params = $qubaids->from_where_params();
550 $params['questionid'] = $questionid;
551
35d5f1c2 552 $records = $DB->get_recordset_sql("
d1b7e03d 553SELECT
04853f27 554 quba.contextid,
d1b7e03d
TH
555 quba.preferredbehaviour,
556 qa.id AS questionattemptid,
557 qa.questionusageid,
558 qa.slot,
559 qa.behaviour,
560 qa.questionid,
1da821bb 561 qa.variant,
d1b7e03d
TH
562 qa.maxmark,
563 qa.minfraction,
564 qa.flagged,
565 qa.questionsummary,
566 qa.rightanswer,
567 qa.responsesummary,
568 qa.timemodified,
569 qas.id AS attemptstepid,
570 qas.sequencenumber,
571 qas.state,
572 qas.fraction,
573 qas.timecreated,
574 qas.userid,
575 qasd.name,
576 qasd.value
577
578FROM {$qubaids->from_question_attempts('qa')}
04853f27
TH
579JOIN {question_usages} quba ON quba.id = qa.questionusageid
580LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
581LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
d1b7e03d
TH
582
583WHERE
584 {$qubaids->where()} AND
04853f27 585 qa.questionid = :questionid
d1b7e03d
TH
586
587ORDER BY
588 quba.id,
589 qa.id,
590 qas.sequencenumber
04853f27 591 ", $params);
d1b7e03d 592
d1b7e03d 593 $questionattempts = array();
35d5f1c2
TH
594 while ($records->valid()) {
595 $record = $records->current();
d1b7e03d
TH
596 $questionattempts[$record->questionattemptid] =
597 question_attempt::load_from_records($records,
598 $record->questionattemptid, new question_usage_null_observer(),
599 $record->preferredbehaviour);
d1b7e03d 600 }
35d5f1c2
TH
601 $records->close();
602
d1b7e03d
TH
603 return $questionattempts;
604 }
605
606 /**
607 * Update a question_usages row to refect any changes in a usage (but not
608 * any of its question_attempts.
609 * @param question_usage_by_activity $quba the usage that has changed.
610 */
611 public function update_questions_usage_by_activity(question_usage_by_activity $quba) {
0ff4bd08 612 $record = new stdClass();
d1b7e03d
TH
613 $record->id = $quba->get_id();
614 $record->contextid = $quba->get_owning_context()->id;
56b0df7e
TH
615 $record->component = $quba->get_owning_component();
616 $record->preferredbehaviour = $quba->get_preferred_behaviour();
d1b7e03d 617
88f0eb15 618 $this->db->update_record('question_usages', $record);
d1b7e03d
TH
619 }
620
621 /**
622 * Update a question_attempts row to refect any changes in a question_attempt
623 * (but not any of its steps).
624 * @param question_attempt $qa the question attempt that has changed.
625 */
626 public function update_question_attempt(question_attempt $qa) {
0ff4bd08 627 $record = new stdClass();
d1b7e03d
TH
628 $record->id = $qa->get_database_id();
629 $record->maxmark = $qa->get_max_mark();
630 $record->minfraction = $qa->get_min_fraction();
631 $record->flagged = $qa->is_flagged();
56b0df7e
TH
632 $record->questionsummary = $qa->get_question_summary();
633 $record->rightanswer = $qa->get_right_answer_summary();
634 $record->responsesummary = $qa->get_response_summary();
d1b7e03d
TH
635 $record->timemodified = time();
636
88f0eb15 637 $this->db->update_record('question_attempts', $record);
d1b7e03d
TH
638 }
639
640 /**
641 * Delete a question_usage_by_activity and all its associated
642 * {@link question_attempts} and {@link question_attempt_steps} from the
643 * database.
6b5f24d3 644 * @param qubaid_condition $qubaids identifies which question useages to delete.
d1b7e03d 645 */
6b5f24d3
TH
646 public function delete_questions_usage_by_activities(qubaid_condition $qubaids) {
647 $where = "qa.questionusageid {$qubaids->usage_id_in()}";
648 $params = $qubaids->usage_id_in_params();
649
650 $contextids = $this->db->get_records_sql_menu("
651 SELECT DISTINCT contextid, 1
652 FROM {question_usages}
a2ac2349 653 WHERE id {$qubaids->usage_id_in()}", $qubaids->usage_id_in_params());
6b5f24d3
TH
654 foreach ($contextids as $contextid => $notused) {
655 $this->delete_response_files($contextid, "IN (
656 SELECT qas.id
657 FROM {question_attempts} qa
658 JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
659 WHERE $where)", $params);
660 }
661
f0bfd964
TH
662 if ($this->db->get_dbfamily() == 'mysql') {
663 $this->delete_usage_records_for_mysql($qubaids);
664 return;
665 }
666
c76145d3 667 $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN (
d1b7e03d 668 SELECT qas.id
c76145d3
TH
669 FROM {question_attempts} qa
670 JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
c76145d3 671 WHERE $where)", $params);
6b5f24d3 672
c76145d3 673 $this->db->delete_records_select('question_attempt_steps', "questionattemptid IN (
d1b7e03d 674 SELECT qa.id
c76145d3 675 FROM {question_attempts} qa
c76145d3 676 WHERE $where)", $params);
6b5f24d3
TH
677
678 $this->db->delete_records_select('question_attempts',
a2ac2349
TH
679 "{question_attempts}.questionusageid {$qubaids->usage_id_in()}",
680 $qubaids->usage_id_in_params());
6b5f24d3
TH
681
682 $this->db->delete_records_select('question_usages',
a2ac2349 683 "{question_usages}.id {$qubaids->usage_id_in()}", $qubaids->usage_id_in_params());
d1b7e03d
TH
684 }
685
f0bfd964
TH
686 /**
687 * This function is a work-around for poor MySQL performance with
688 * DELETE FROM x WHERE id IN (SELECT ...). We have to use a non-standard
689 * syntax to get good performance. See MDL-29520.
690 * @param qubaid_condition $qubaids identifies which question useages to delete.
691 */
692 protected function delete_usage_records_for_mysql(qubaid_condition $qubaids) {
693 // TODO once MDL-29589 is fixed, eliminate this method, and instead use the new $DB API.
694 $this->db->execute('
695 DELETE qu, qa, qas, qasd
696 FROM {question_usages} qu
697 JOIN {question_attempts} qa ON qa.questionusageid = qu.id
033d6566
EL
698 LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
699 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
f0bfd964
TH
700 WHERE qu.id ' . $qubaids->usage_id_in(),
701 $qubaids->usage_id_in_params());
702 }
703
704 /**
705 * This function is a work-around for poor MySQL performance with
706 * DELETE FROM x WHERE id IN (SELECT ...). We have to use a non-standard
707 * syntax to get good performance. See MDL-29520.
708 * @param string $test sql fragment.
709 * @param array $params used by $test.
710 */
711 protected function delete_attempt_steps_for_mysql($test, $params) {
712 // TODO once MDL-29589 is fixed, eliminate this method, and instead use the new $DB API.
713 $this->db->execute('
714 DELETE qas, qasd
715 FROM {question_attempt_steps} qas
033d6566 716 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
f0bfd964
TH
717 WHERE qas.questionattemptid ' . $test, $params);
718 }
719
d1b7e03d
TH
720 /**
721 * Delete all the steps for a question attempt.
f7970e3c 722 * @param int $qaids question_attempt id.
d1b7e03d 723 */
6b5f24d3 724 public function delete_steps_for_question_attempts($qaids, $context) {
d1b7e03d
TH
725 if (empty($qaids)) {
726 return;
727 }
6b5f24d3
TH
728 list($test, $params) = $this->db->get_in_or_equal($qaids, SQL_PARAMS_NAMED);
729
730 $this->delete_response_files($context->id, "IN (
731 SELECT id
3b049b76 732 FROM {question_attempt_steps}
6b5f24d3
TH
733 WHERE questionattemptid $test)", $params);
734
f0bfd964
TH
735 if ($this->db->get_dbfamily() == 'mysql') {
736 $this->delete_attempt_steps_for_mysql($test, $params);
737 return;
738 }
739
c76145d3 740 $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN (
d1b7e03d 741 SELECT qas.id
c76145d3
TH
742 FROM {question_attempt_steps} qas
743 WHERE questionattemptid $test)", $params);
9c197f44
TH
744 $this->db->delete_records_select('question_attempt_steps',
745 'questionattemptid ' . $test, $params);
d1b7e03d
TH
746 }
747
6b5f24d3
TH
748 /**
749 * Delete all the files belonging to the response variables in the gives
750 * question attempt steps.
751 * @param int $contextid the context these attempts belong to.
752 * @param string $itemidstest a bit of SQL that can be used in a
753 * WHERE itemid $itemidstest clause. Must use named params.
754 * @param array $params any query parameters used in $itemidstest.
755 */
756 protected function delete_response_files($contextid, $itemidstest, $params) {
757 $fs = get_file_storage();
c749527b 758 foreach (question_engine::get_all_response_file_areas() as $filearea) {
6b5f24d3
TH
759 $fs->delete_area_files_select($contextid, 'question', $filearea,
760 $itemidstest, $params);
761 }
762 }
763
d1b7e03d
TH
764 /**
765 * Delete all the previews for a given question.
f7970e3c 766 * @param int $questionid question id.
d1b7e03d
TH
767 */
768 public function delete_previews($questionid) {
c76145d3 769 $previews = $this->db->get_records_sql_menu("
d1b7e03d 770 SELECT DISTINCT quba.id, 1
c76145d3
TH
771 FROM {question_usages} quba
772 JOIN {question_attempts} qa ON qa.questionusageid = quba.id
d1b7e03d 773 WHERE quba.component = 'core_question_preview' AND
c76145d3 774 qa.questionid = ?", array($questionid));
d1b7e03d
TH
775 if (empty($previews)) {
776 return;
777 }
6b5f24d3 778 $this->delete_questions_usage_by_activities(new qubaid_list($previews));
d1b7e03d
TH
779 }
780
781 /**
782 * Update the flagged state of a question in the database.
f7970e3c
TH
783 * @param int $qubaid the question usage id.
784 * @param int $questionid the question id.
785 * @param int $sessionid the question_attempt id.
786 * @param bool $newstate the new state of the flag. true = flagged.
d1b7e03d 787 */
06f8ed54 788 public function update_question_attempt_flag($qubaid, $questionid, $qaid, $slot, $newstate) {
f9b0500f 789 if (!$this->db->record_exists('question_attempts', array('id' => $qaid,
06f8ed54 790 'questionusageid' => $qubaid, 'questionid' => $questionid, 'slot' => $slot))) {
88f0eb15 791 throw new moodle_exception('errorsavingflags', 'question');
d1b7e03d
TH
792 }
793
06f8ed54 794 $this->db->set_field('question_attempts', 'flagged', $newstate, array('id' => $qaid));
d1b7e03d
TH
795 }
796
797 /**
798 * Get all the WHEN 'x' THEN 'y' terms needed to convert the question_attempt_steps.state
799 * column to a summary state. Use this like
800 * CASE qas.state {$this->full_states_to_summary_state_sql()} END AS summarystate,
801 * @param string SQL fragment.
802 */
803 protected function full_states_to_summary_state_sql() {
804 $sql = '';
805 foreach (question_state::get_all() as $state) {
806 $sql .= "WHEN '$state' THEN '{$state->get_summary_state()}'\n";
807 }
808 return $sql;
809 }
810
811 /**
812 * Get the SQL needed to test that question_attempt_steps.state is in a
813 * state corresponding to $summarystate.
814 * @param string $summarystate one of
815 * inprogress, needsgrading, manuallygraded or autograded
f7970e3c 816 * @param bool $equal if false, do a NOT IN test. Default true.
d1b7e03d
TH
817 * @return string SQL fragment.
818 */
cf3b6568 819 public function in_summary_state_test($summarystate, $equal = true, $prefix = 'summarystates') {
d1b7e03d 820 $states = question_state::get_all_for_summary_state($summarystate);
a2ac2349 821 return $this->db->get_in_or_equal($states, SQL_PARAMS_NAMED, $prefix, $equal);
d1b7e03d
TH
822 }
823
824 /**
825 * Change the maxmark for the question_attempt with number in usage $slot
826 * for all the specified question_attempts.
827 * @param qubaid_condition $qubaids Selects which usages are updated.
f7970e3c 828 * @param int $slot the number is usage to affect.
d1b7e03d
TH
829 * @param number $newmaxmark the new max mark to set.
830 */
831 public function set_max_mark_in_attempts(qubaid_condition $qubaids, $slot, $newmaxmark) {
c76145d3
TH
832 $this->db->set_field_select('question_attempts', 'maxmark', $newmaxmark,
833 "questionusageid {$qubaids->usage_id_in()} AND slot = :slot",
834 $qubaids->usage_id_in_params() + array('slot' => $slot));
d1b7e03d
TH
835 }
836
837 /**
838 * Return a subquery that computes the sum of the marks for all the questions
839 * in a usage. Which useage to compute the sum for is controlled bu the $qubaid
840 * parameter.
841 *
842 * See {@link quiz_update_all_attempt_sumgrades()} for an example of the usage of
843 * this method.
844 *
845 * @param string $qubaid SQL fragment that controls which usage is summed.
2daffca5
TH
846 * This will normally be the name of a column in the outer query. Not that this
847 * SQL fragment must not contain any placeholders.
d1b7e03d
TH
848 * @return string SQL code for the subquery.
849 */
850 public function sum_usage_marks_subquery($qubaid) {
d1b7e03d 851 return "SELECT SUM(qa.maxmark * qas.fraction)
c76145d3 852 FROM {question_attempts} qa
d2c69d93
TH
853 JOIN {question_attempt_steps} qas ON qas.id = (
854 SELECT MAX(summarks_qas.id)
855 FROM {question_attempt_steps} summarks_qas
856 WHERE summarks_qas.questionattemptid = qa.id
857 )
d1b7e03d 858 WHERE qa.questionusageid = $qubaid
9c197f44
TH
859 HAVING COUNT(CASE
860 WHEN qas.state = 'needsgrading' AND qa.maxmark > 0 THEN 1
861 ELSE NULL
862 END) = 0";
d1b7e03d
TH
863 }
864
865 public function question_attempt_latest_state_view($alias) {
d1b7e03d
TH
866 return "(
867 SELECT
868 {$alias}qa.id AS questionattemptid,
869 {$alias}qa.questionusageid,
870 {$alias}qa.slot,
871 {$alias}qa.behaviour,
872 {$alias}qa.questionid,
1da821bb 873 {$alias}qa.variant,
d1b7e03d
TH
874 {$alias}qa.maxmark,
875 {$alias}qa.minfraction,
876 {$alias}qa.flagged,
877 {$alias}qa.questionsummary,
878 {$alias}qa.rightanswer,
879 {$alias}qa.responsesummary,
880 {$alias}qa.timemodified,
881 {$alias}qas.id AS attemptstepid,
882 {$alias}qas.sequencenumber,
883 {$alias}qas.state,
884 {$alias}qas.fraction,
885 {$alias}qas.timecreated,
886 {$alias}qas.userid
887
2a3bdbf9
TH
888 FROM {question_attempts} {$alias}qa
889 JOIN {question_attempt_steps} {$alias}qas ON
d1b7e03d
TH
890 {$alias}qas.id = {$this->latest_step_for_qa_subquery($alias . 'qa.id')}
891 ) $alias";
892 }
893
894 protected function latest_step_for_qa_subquery($questionattemptid = 'qa.id') {
d1b7e03d
TH
895 return "(
896 SELECT MAX(id)
c76145d3 897 FROM {question_attempt_steps}
d1b7e03d
TH
898 WHERE questionattemptid = $questionattemptid
899 )";
900 }
901
902 /**
903 * @param array $questionids of question ids.
07f88584
TH
904 * @param qubaid_condition $qubaids ids of the usages to consider.
905 * @return boolean whether any of these questions are being used by any of
906 * those usages.
d1b7e03d 907 */
07f88584 908 public function questions_in_use(array $questionids, qubaid_condition $qubaids) {
c76145d3
TH
909 list($test, $params) = $this->db->get_in_or_equal($questionids);
910 return $this->db->record_exists_select('question_attempts',
07f88584
TH
911 'questionid ' . $test . ' AND questionusageid ' .
912 $qubaids->usage_id_in(), $params + $qubaids->usage_id_in_params());
d1b7e03d
TH
913 }
914}
915
6b5f24d3 916
d1b7e03d
TH
917/**
918 * Implementation of the unit of work pattern for the question engine.
919 *
920 * See http://martinfowler.com/eaaCatalog/unitOfWork.html. This tracks all the
921 * changes to a {@link question_usage_by_activity}, and its constituent parts,
922 * so that the changes can be saved to the database when {@link save()} is called.
923 *
017bc1d9
TH
924 * @copyright 2009 The Open University
925 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
926 */
927class question_engine_unit_of_work implements question_usage_observer {
928 /** @var question_usage_by_activity the usage being tracked. */
929 protected $quba;
930
931 /** @var boolean whether any of the fields of the usage have been changed. */
932 protected $modified = false;
933
934 /**
935 * @var array list of number in usage => {@link question_attempt}s that
936 * were already in the usage, and which have been modified.
937 */
938 protected $attemptsmodified = array();
939
940 /**
941 * @var array list of number in usage => {@link question_attempt}s that
942 * have been added to the usage.
943 */
944 protected $attemptsadded = array();
945
946 /**
947 * @var array list of question attempt ids to delete the steps for, before
948 * inserting new steps.
949 */
950 protected $attemptstodeletestepsfor = array();
951
952 /**
953 * @var array list of array(question_attempt_step, question_attempt id, seq number)
954 * of steps that have been added to question attempts in this usage.
955 */
956 protected $stepsadded = array();
957
958 /**
959 * Constructor.
960 * @param question_usage_by_activity $quba the usage to track.
961 */
962 public function __construct(question_usage_by_activity $quba) {
963 $this->quba = $quba;
964 }
965
966 public function notify_modified() {
967 $this->modified = true;
968 }
969
970 public function notify_attempt_modified(question_attempt $qa) {
971 $no = $qa->get_slot();
972 if (!array_key_exists($no, $this->attemptsadded)) {
973 $this->attemptsmodified[$no] = $qa;
974 }
975 }
976
977 public function notify_attempt_added(question_attempt $qa) {
978 $this->attemptsadded[$qa->get_slot()] = $qa;
979 }
980
981 public function notify_delete_attempt_steps(question_attempt $qa) {
982
983 if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
984 return;
985 }
986
987 $qaid = $qa->get_database_id();
988 foreach ($this->stepsadded as $key => $stepinfo) {
989 if ($stepinfo[1] == $qaid) {
990 unset($this->stepsadded[$key]);
991 }
992 }
993
994 $this->attemptstodeletestepsfor[$qaid] = 1;
995 }
996
997 public function notify_step_added(question_attempt_step $step, question_attempt $qa, $seq) {
998 if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
999 return;
1000 }
1001 $this->stepsadded[] = array($step, $qa->get_database_id(), $seq);
1002 }
1003
1004 /**
1005 * Write all the changes we have recorded to the database.
1006 * @param question_engine_data_mapper $dm the mapper to use to update the database.
1007 */
1008 public function save(question_engine_data_mapper $dm) {
6b5f24d3
TH
1009 $dm->delete_steps_for_question_attempts(array_keys($this->attemptstodeletestepsfor),
1010 $this->quba->get_owning_context());
1011
d1b7e03d
TH
1012 foreach ($this->stepsadded as $stepinfo) {
1013 list($step, $questionattemptid, $seq) = $stepinfo;
217f9a61
TH
1014 $dm->insert_question_attempt_step($step, $questionattemptid, $seq,
1015 $this->quba->get_owning_context());
d1b7e03d 1016 }
6b5f24d3 1017
d1b7e03d 1018 foreach ($this->attemptsadded as $qa) {
217f9a61 1019 $dm->insert_question_attempt($qa, $this->quba->get_owning_context());
d1b7e03d 1020 }
6b5f24d3 1021
d1b7e03d
TH
1022 foreach ($this->attemptsmodified as $qa) {
1023 $dm->update_question_attempt($qa);
1024 }
6b5f24d3 1025
d1b7e03d
TH
1026 if ($this->modified) {
1027 $dm->update_questions_usage_by_activity($this->quba);
1028 }
1029 }
1030}
1031
1032
217f9a61
TH
1033/**
1034 * This class represents the promise to save some files from a particular draft
1035 * file area into a particular file area. It is used beause the necessary
1036 * information about what to save is to hand in the
1037 * {@link question_attempt::process_response_files()} method, but we don't know
1038 * if this question attempt will actually be saved in the database until later,
1039 * when the {@link question_engine_unit_of_work} is saved, if it is.
1040 *
1041 * @copyright 2011 The Open University
1042 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1043 */
1044class question_file_saver {
1045 /** @var int the id of the draft file area to save files from. */
1046 protected $draftitemid;
1047 /** @var string the owning component name. */
1048 protected $component;
1049 /** @var string the file area name. */
1050 protected $filearea;
1051
cd3557e6
TH
1052 /**
1053 * @var string the value to store in the question_attempt_step_data to
1054 * represent these files.
1055 */
1056 protected $value = null;
1057
217f9a61
TH
1058 /**
1059 * Constuctor.
1060 * @param int $draftitemid the draft area to save the files from.
1061 * @param string $component the component for the file area to save into.
1062 * @param string $filearea the name of the file area to save into.
1063 */
48d9c17d 1064 public function __construct($draftitemid, $component, $filearea, $text = null) {
217f9a61
TH
1065 $this->draftitemid = $draftitemid;
1066 $this->component = $component;
1067 $this->filearea = $filearea;
48d9c17d 1068 $this->value = $this->compute_value($draftitemid, $text);
217f9a61
TH
1069 }
1070
48d9c17d
TH
1071 /**
1072 * Compute the value that should be stored in the question_attempt_step_data
1073 * table. Contains a hash that (almost) uniquely encodes all the files.
1074 * @param int $draftitemid the draft file area itemid.
1075 * @param string $text optional content containing file links.
1076 */
1077 protected function compute_value($draftitemid, $text) {
cd3557e6
TH
1078 global $USER;
1079
cd3557e6
TH
1080 $fs = get_file_storage();
1081 $usercontext = get_context_instance(CONTEXT_USER, $USER->id);
1082
1083 $files = $fs->get_area_files($usercontext->id, 'user', 'draft',
48d9c17d 1084 $draftitemid, 'sortorder, filepath, filename', false);
cd3557e6
TH
1085
1086 $string = '';
1087 foreach ($files as $file) {
1088 $string .= $file->get_filepath() . $file->get_filename() . '|' .
1089 $file->get_contenthash() . '|';
1090 }
1091
1092 if ($string) {
48d9c17d 1093 $hash = md5($string);
cd3557e6 1094 } else {
48d9c17d 1095 $hash = '';
cd3557e6
TH
1096 }
1097
48d9c17d
TH
1098 if (is_null($text)) {
1099 return $hash;
1100 }
1101
1102 // We add the file hash so a simple string comparison will say if the
1103 // files have been changed. First strip off any existing file hash.
1104 $text = preg_replace('/\s*<!-- File hash: \w+ -->\s*$/', '', $text);
1105 $text = file_rewrite_urls_to_pluginfile($text, $draftitemid);
1106 if ($hash) {
1107 $text .= '<!-- File hash: ' . $hash . ' -->';
1108 }
1109 return $text;
cd3557e6
TH
1110 }
1111
217f9a61 1112 public function __toString() {
48d9c17d 1113 return $this->value;
217f9a61
TH
1114 }
1115
1116 /**
1117 * Actually save the files.
1118 * @param integer $itemid the item id for the file area to save into.
1119 */
1120 public function save_files($itemid, $context) {
1121 file_save_draft_area_files($this->draftitemid, $context->id,
1122 $this->component, $this->filearea, $itemid);
1123 }
1124}
1125
1126
d1b7e03d
TH
1127/**
1128 * This class represents a restriction on the set of question_usage ids to include
1129 * in a larger database query. Depending of the how you are going to restrict the
1130 * list of usages, construct an appropriate subclass.
1131 *
1132 * If $qubaids is an instance of this class, example usage might be
1133 *
1134 * SELECT qa.id, qa.maxmark
1135 * FROM $qubaids->from_question_attempts('qa')
1136 * WHERE $qubaids->where() AND qa.slot = 1
1137 *
f7970e3c 1138 * @copyright 2010 The Open University
017bc1d9 1139 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
1140 */
1141abstract class qubaid_condition {
1142
1143 /**
1144 * @return string the SQL that needs to go in the FROM clause when trying
1145 * to select records from the 'question_attempts' table based on the
1146 * qubaid_condition.
1147 */
1148 public abstract function from_question_attempts($alias);
1149
1150 /** @return string the SQL that needs to go in the where clause. */
1151 public abstract function where();
1152
1153 /**
1154 * @return the params needed by a query that uses
1155 * {@link from_question_attempts()} and {@link where()}.
1156 */
1157 public abstract function from_where_params();
1158
1159 /**
1160 * @return string SQL that can use used in a WHERE qubaid IN (...) query.
1161 * This method returns the "IN (...)" part.
1162 */
1163 public abstract function usage_id_in();
1164
1165 /**
1166 * @return the params needed by a query that uses {@link usage_id_in()}.
1167 */
1168 public abstract function usage_id_in_params();
1169}
1170
1171
1172/**
1173 * This class represents a restriction on the set of question_usage ids to include
1174 * in a larger database query based on an explicit list of ids.
1175 *
f7970e3c 1176 * @copyright 2010 The Open University
017bc1d9 1177 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
1178 */
1179class qubaid_list extends qubaid_condition {
1180 /** @var array of ids. */
1181 protected $qubaids;
1182 protected $columntotest = null;
1183 protected $params;
1184
1185 /**
1186 * Constructor.
1187 * @param array $qubaids of question usage ids.
1188 */
1189 public function __construct(array $qubaids) {
1190 $this->qubaids = $qubaids;
1191 }
1192
1193 public function from_question_attempts($alias) {
d1b7e03d 1194 $this->columntotest = $alias . '.questionusageid';
9b40c540 1195 return '{question_attempts} ' . $alias;
d1b7e03d
TH
1196 }
1197
1198 public function where() {
1199 global $DB;
1200
1201 if (is_null($this->columntotest)) {
88f0eb15 1202 throw new coding_exception('Must call from_question_attempts before where().');
d1b7e03d
TH
1203 }
1204 if (empty($this->qubaids)) {
9b40c540 1205 $this->params = array();
d1b7e03d
TH
1206 return '1 = 0';
1207 }
d1b7e03d 1208
9b40c540 1209 return $this->columntotest . ' ' . $this->usage_id_in();
d1b7e03d
TH
1210 }
1211
1212 public function from_where_params() {
1213 return $this->params;
1214 }
1215
1216 public function usage_id_in() {
1217 global $DB;
1218
1219 if (empty($this->qubaids)) {
5f79a9bc 1220 $this->params = array();
d1b7e03d
TH
1221 return '= 0';
1222 }
9c197f44
TH
1223 list($where, $this->params) = $DB->get_in_or_equal(
1224 $this->qubaids, SQL_PARAMS_NAMED, 'qubaid');
d1b7e03d
TH
1225 return $where;
1226 }
1227
1228 public function usage_id_in_params() {
1229 return $this->params;
1230 }
1231}
1232
1233
1234/**
1235 * This class represents a restriction on the set of question_usage ids to include
1236 * in a larger database query based on JOINing to some other tables.
1237 *
1238 * The general form of the query is something like
1239 *
1240 * SELECT qa.id, qa.maxmark
1241 * FROM $from
2a3bdbf9 1242 * JOIN {question_attempts} qa ON qa.questionusageid = $usageidcolumn
d1b7e03d
TH
1243 * WHERE $where AND qa.slot = 1
1244 *
1245 * where $from, $usageidcolumn and $where are the arguments to the constructor.
1246 *
f7970e3c 1247 * @copyright 2010 The Open University
017bc1d9 1248 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
1249 */
1250class qubaid_join extends qubaid_condition {
1251 public $from;
1252 public $usageidcolumn;
1253 public $where;
1254 public $params;
1255
1256 /**
1257 * Constructor. The meaning of the arguments is explained in the class comment.
1258 * @param string $from SQL fragemnt to go in the FROM clause.
1259 * @param string $usageidcolumn the column in $from that should be
1260 * made equal to the usageid column in the JOIN clause.
1261 * @param string $where SQL fragment to go in the where clause.
e24ee794 1262 * @param array $params required by the SQL. You must use named parameters.
d1b7e03d
TH
1263 */
1264 public function __construct($from, $usageidcolumn, $where = '', $params = array()) {
1265 $this->from = $from;
1266 $this->usageidcolumn = $usageidcolumn;
1267 $this->params = $params;
1268 if (empty($where)) {
1269 $where = '1 = 1';
1270 }
1271 $this->where = $where;
1272 }
1273
1274 public function from_question_attempts($alias) {
d1b7e03d 1275 return "$this->from
0f33deaf 1276 JOIN {question_attempts} {$alias} ON " .
d1b7e03d
TH
1277 "{$alias}.questionusageid = $this->usageidcolumn";
1278 }
1279
1280 public function where() {
1281 return $this->where;
1282 }
1283
1284 public function from_where_params() {
1285 return $this->params;
1286 }
1287
1288 public function usage_id_in() {
1289 return "IN (SELECT $this->usageidcolumn FROM $this->from WHERE $this->where)";
1290 }
1291
1292 public function usage_id_in_params() {
1293 return $this->params;
1294 }
1295}