MDL-49231 mod_glossary: Fix SQL queries for Oracle
[moodle.git] / mod / glossary / classes / entry_query_builder.php
CommitLineData
3bb15852
FM
1<?php
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
17/**
18 * Entry query builder.
19 *
20 * @package mod_glossary
21 * @copyright 2015 Frédéric Massart - FMCorz.net
22 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
23 */
24
25defined('MOODLE_INTERNAL') || die();
26
27/**
28 * Entry query builder class.
29 *
30 * The purpose of this class is to avoid duplicating SQL statements to fetch entries
31 * which are very similar with each other. This builder is not meant to be smart, it
32 * will not out rule any previously set condition, or join, etc...
33 *
34 * You should be using this builder just like you would be creating your SQL query. Only
35 * some methods are shorthands to avoid logic duplication and common mistakes.
36 *
37 * @package mod_glossary
38 * @copyright 2015 Frédéric Massart - FMCorz.net
39 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
a4d9adcb 40 * @since Moodle 3.1
3bb15852
FM
41 */
42class mod_glossary_entry_query_builder {
43
24777f7a
FM
44 /** Alias for table glossary_alias. */
45 const ALIAS_ALIAS = 'ga';
3bb15852
FM
46 /** Alias for table glossary_categories. */
47 const ALIAS_CATEGORIES = 'gc';
48 /** Alias for table glossary_entries_categories. */
49 const ALIAS_ENTRIES_CATEGORIES = 'gec';
50 /** Alias for table glossary_entries. */
51 const ALIAS_ENTRIES = 'ge';
52 /** Alias for table user. */
53 const ALIAS_USER = 'u';
54
55 /** Include none of the entries to approve. */
56 const NON_APPROVED_NONE = 'na_none';
57 /** Including all the entries. */
58 const NON_APPROVED_ALL = 'na_all';
59 /** Including only the entries to be approved. */
60 const NON_APPROVED_ONLY = 'na_only';
61 /** Including my entries to be approved. */
62 const NON_APPROVED_SELF = 'na_self';
63
64 /** @var array Raw SQL statements representing the fields to select. */
65 protected $fields = array();
66 /** @var array Raw SQL statements representing the JOINs to make. */
67 protected $joins = array();
68 /** @var string Raw SQL statement representing the FROM clause. */
69 protected $from;
70 /** @var object The glossary we are fetching from. */
71 protected $glossary;
72 /** @var int The number of records to fetch from. */
73 protected $limitfrom = 0;
74 /** @var int The number of records to fetch. */
75 protected $limitnum = 0;
76 /** @var array List of SQL parameters. */
77 protected $params = array();
78 /** @var array Raw SQL statements representing the ORDER clause. */
79 protected $order = array();
80 /** @var array Raw SQL statements representing the WHERE clause. */
81 protected $where = array();
82
83 /**
84 * Constructor.
85 *
86 * @param object $glossary The glossary.
87 */
08d7954f 88 public function __construct($glossary = null) {
3bb15852 89 $this->from = sprintf('FROM {glossary_entries} %s', self::ALIAS_ENTRIES);
08d7954f
FM
90 if (!empty($glossary)) {
91 $this->glossary = $glossary;
a4d9adcb
FM
92 $this->where[] = sprintf('(%s.glossaryid = :gid OR %s.sourceglossaryid = :gid2)',
93 self::ALIAS_ENTRIES, self::ALIAS_ENTRIES);
08d7954f
FM
94 $this->params['gid'] = $glossary->id;
95 $this->params['gid2'] = $glossary->id;
96 }
3bb15852
FM
97 }
98
99 /**
100 * Add a field to select.
101 *
102 * @param string $field The field, or *.
103 * @param string $table The table name, without the prefix 'glossary_'.
104 * @param string $alias An alias for the field.
105 */
106 public function add_field($field, $table, $alias = null) {
107 $field = self::resolve_field($field, $table);
108 if (!empty($alias)) {
109 $field .= ' AS ' . $alias;
110 }
111 $this->fields[] = $field;
112 }
113
114 /**
115 * Adds the user fields.
116 *
117 * @return void
118 */
119 public function add_user_fields() {
120 $this->fields[] = user_picture::fields('u', null, 'userdataid', 'userdata');
121 }
122
123 /**
124 * Internal method to build the query.
125 *
a4d9adcb 126 * @param bool $count Query to count?
3bb15852
FM
127 * @return string The SQL statement.
128 */
129 protected function build_query($count = false) {
130 $sql = 'SELECT ';
131
132 if ($count) {
133 $sql .= 'COUNT(\'x\') ';
134 } else {
135 $sql .= implode(', ', $this->fields) . ' ';
136 }
137
138 $sql .= $this->from . ' ';
139 $sql .= implode(' ', $this->joins) . ' ';
140
141 if (!empty($this->where)) {
142 $sql .= 'WHERE (' . implode(') AND (', $this->where) . ') ';
143 }
144
145 if (!$count && !empty($this->order)) {
146 $sql .= 'ORDER BY ' . implode(', ', $this->order);
147 }
148
149 return $sql;
150 }
151
152 /**
153 * Count the records.
154 *
a4d9adcb 155 * @return int The number of records.
3bb15852
FM
156 */
157 public function count_records() {
158 global $DB;
159 return $DB->count_records_sql($this->build_query(true), $this->params);
160 }
161
162 /**
163 * Filter a field using a letter.
164 *
a4d9adcb
FM
165 * @param string $letter The letter.
166 * @param string $finalfield The SQL statement representing the field.
3bb15852
FM
167 */
168 protected function filter_by_letter($letter, $finalfield) {
169 global $DB;
170
171 $letter = core_text::strtoupper($letter);
172 $len = core_text::strlen($letter);
173 $sql = $DB->sql_substr(sprintf('upper(%s)', $finalfield), 1, $len);
174
175 $this->where[] = "$sql = :letter";
176 $this->params['letter'] = $letter;
177 }
178
179 /**
180 * Filter a field by special characters.
181 *
a4d9adcb 182 * @param string $finalfield The SQL statement representing the field.
3bb15852
FM
183 */
184 protected function filter_by_non_letter($finalfield) {
185 global $DB;
186
187 $alphabet = explode(',', get_string('alphabet', 'langconfig'));
188 list($nia, $aparams) = $DB->get_in_or_equal($alphabet, SQL_PARAMS_NAMED, 'nonletter', false);
189
190 $sql = $DB->sql_substr(sprintf('upper(%s)', $finalfield), 1, 1);
191
192 $this->where[] = "$sql $nia";
193 $this->params = array_merge($this->params, $aparams);
194 }
195
196 /**
197 * Filter the author by letter.
198 *
a4d9adcb
FM
199 * @param string $letter The letter.
200 * @param bool $firstnamefirst Whether or not the firstname is first in the author's name.
3bb15852
FM
201 */
202 public function filter_by_author_letter($letter, $firstnamefirst = false) {
203 $field = self::get_fullname_field($firstnamefirst);
204 $this->filter_by_letter($letter, $field);
205 }
206
207 /**
208 * Filter the author by special characters.
209 *
a4d9adcb 210 * @param bool $firstnamefirst Whether or not the firstname is first in the author's name.
3bb15852
FM
211 */
212 public function filter_by_author_non_letter($firstnamefirst = false) {
213 $field = self::get_fullname_field($firstnamefirst);
214 $this->filter_by_non_letter($field);
215 }
216
217 /**
218 * Filter the concept by letter.
219 *
a4d9adcb 220 * @param string $letter The letter.
3bb15852
FM
221 */
222 public function filter_by_concept_letter($letter) {
223 $this->filter_by_letter($letter, self::resolve_field('concept', 'entries'));
224 }
225
226 /**
227 * Filter the concept by special characters.
228 *
229 * @return void
230 */
231 public function filter_by_concept_non_letter() {
232 $this->filter_by_non_letter(self::resolve_field('concept', 'entries'));
233 }
234
235 /**
236 * Filter non approved entries.
237 *
a4d9adcb
FM
238 * @param string $constant One of the NON_APPROVED_* constants.
239 * @param int $userid The user ID when relevant, otherwise current user.
3bb15852
FM
240 */
241 public function filter_by_non_approved($constant, $userid = null) {
242 global $USER;
243 if (!$userid) {
244 $userid = $USER->id;
245 }
246
247 if ($constant === self::NON_APPROVED_ALL) {
248 // Nothing to do.
249
250 } else if ($constant === self::NON_APPROVED_SELF) {
251 $this->where[] = sprintf('%s != 0 OR %s = :toapproveuserid',
252 self::resolve_field('approved', 'entries'), self::resolve_field('userid', 'entries'));
253 $this->params['toapproveuserid'] = $USER->id;
254
255 } else if ($constant === self::NON_APPROVED_NONE) {
256 $this->where[] = sprintf('%s != 0', self::resolve_field('approved', 'entries'));
257
258 } else if ($constant === self::NON_APPROVED_ONLY) {
259 $this->where[] = sprintf('%s = 0', self::resolve_field('approved', 'entries'));
260
261 } else {
262 throw new coding_exception('Invalid constant');
263 }
264 }
265
93e122e0
FM
266 /**
267 * Filter by concept or alias.
268 *
269 * This requires the alias table to be joined in the query. See {@link self::join_alias()}.
270 *
a4d9adcb 271 * @param string $term What the concept or aliases should be.
93e122e0
FM
272 */
273 public function filter_by_term($term) {
274 $this->where[] = sprintf("(%s = :filterterma OR %s = :filtertermb)",
275 self::resolve_field('concept', 'entries'),
276 self::resolve_field('alias', 'alias'));
277 $this->params['filterterma'] = $term;
278 $this->params['filtertermb'] = $term;
279 }
280
3bb15852
FM
281 /**
282 * Convenience method to get get the SQL statement for the full name.
283 *
a4d9adcb 284 * @param bool $firstnamefirst Whether or not the firstname is first in the author's name.
3bb15852
FM
285 * @return string The SQL statement.
286 */
287 public static function get_fullname_field($firstnamefirst = false) {
288 global $DB;
289 if ($firstnamefirst) {
290 return $DB->sql_fullname(self::resolve_field('firstname', 'user'), self::resolve_field('lastname', 'user'));
291 }
292 return $DB->sql_fullname(self::resolve_field('lastname', 'user'), self::resolve_field('firstname', 'user'));
293 }
294
295 /**
296 * Get the records.
297 *
298 * @return array
299 */
300 public function get_records() {
301 global $DB;
302 return $DB->get_records_sql($this->build_query(), $this->params, $this->limitfrom, $this->limitnum);
303 }
304
305 /**
306 * Get the recordset.
307 *
308 * @return moodle_recordset
309 */
310 public function get_recordset() {
311 global $DB;
312 return $DB->get_recordset_sql($this->build_query(), $this->params, $this->limitfrom, $this->limitnum);
313 }
314
315 /**
316 * Retrieve a user object from a record.
317 *
318 * This comes handy when {@link self::add_user_fields} was used.
319 *
a4d9adcb 320 * @param stdClass $record The record.
3bb15852
FM
321 * @return stdClass A user object.
322 */
323 public static function get_user_from_record($record) {
324 return user_picture::unalias($record, null, 'userdataid', 'userdata');
325 }
326
24777f7a
FM
327 /**
328 * Join the alias table.
329 *
330 * Note that this may cause the same entry to be returned more than once. You might want
06d58e6e 331 * to add a distinct on the entry id.
24777f7a
FM
332 *
333 * @return void
334 */
335 public function join_alias() {
336 $this->joins[] = sprintf('LEFT JOIN {glossary_alias} %s ON %s = %s',
337 self::ALIAS_ALIAS, self::resolve_field('id', 'entries'), self::resolve_field('entryid', 'alias'));
338 }
339
3bb15852
FM
340 /**
341 * Join on the category tables.
342 *
343 * Depending on the category passed the joins will be different. This is due to the display
344 * logic that assumes that when displaying all categories the non categorised entries should
345 * not be returned, etc...
346 *
a4d9adcb 347 * @param int $categoryid The category ID, or GLOSSARY_SHOW_* constant.
3bb15852
FM
348 */
349 public function join_category($categoryid) {
350
351 if ($categoryid === GLOSSARY_SHOW_ALL_CATEGORIES) {
352 $this->joins[] = sprintf('JOIN {glossary_entries_categories} %s ON %s = %s',
353 self::ALIAS_ENTRIES_CATEGORIES, self::resolve_field('id', 'entries'),
354 self::resolve_field('entryid', 'entries_categories'));
355
356 $this->joins[] = sprintf('JOIN {glossary_categories} %s ON %s = %s',
357 self::ALIAS_CATEGORIES, self::resolve_field('id', 'categories'),
358 self::resolve_field('categoryid', 'entries_categories'));
359
360 } else if ($categoryid === GLOSSARY_SHOW_NOT_CATEGORISED) {
361 $this->joins[] = sprintf('LEFT JOIN {glossary_entries_categories} %s ON %s = %s',
362 self::ALIAS_ENTRIES_CATEGORIES, self::resolve_field('id', 'entries'),
363 self::resolve_field('entryid', 'entries_categories'));
364
365 } else {
366 $this->joins[] = sprintf('JOIN {glossary_entries_categories} %s ON %s = %s AND %s = :joincategoryid',
367 self::ALIAS_ENTRIES_CATEGORIES, self::resolve_field('id', 'entries'),
368 self::resolve_field('entryid', 'entries_categories'),
369 self::resolve_field('categoryid', 'entries_categories'));
370 $this->params['joincategoryid'] = $categoryid;
371
372 }
373 }
374
375 /**
376 * Join the user table.
377 *
a4d9adcb 378 * @param bool $strict When strict uses a JOIN rather than a LEFT JOIN.
3bb15852
FM
379 */
380 public function join_user($strict = false) {
381 $join = $strict ? 'JOIN' : 'LEFT JOIN';
382 $this->joins[] = sprintf("$join {user} %s ON %s = %s",
383 self::ALIAS_USER, self::resolve_field('id', 'user'), self::resolve_field('userid', 'entries'));
384 }
385
386 /**
387 * Limit the number of records to fetch.
a4d9adcb
FM
388 * @param int $from Fetch from.
389 * @param int $num Number to fetch.
3bb15852
FM
390 */
391 public function limit($from, $num) {
392 $this->limitfrom = $from;
393 $this->limitnum = $num;
394 }
395
396 /**
397 * Normalise a direction.
398 *
399 * This ensures that the value is either ASC or DESC.
400 *
a4d9adcb 401 * @param string $direction The desired direction.
3bb15852
FM
402 * @return string ASC or DESC.
403 */
404 protected function normalize_direction($direction) {
405 $direction = core_text::strtoupper($direction);
406 if ($direction == 'DESC') {
407 return 'DESC';
408 }
409 return 'ASC';
410 }
411
412 /**
413 * Order by a field.
414 *
415 * @param string $field The field, or *.
416 * @param string $table The table name, without the prefix 'glossary_'.
417 * @param string $direction ASC, or DESC.
418 */
419 public function order_by($field, $table, $direction = '') {
420 $direction = self::normalize_direction($direction);
421 $this->order[] = self::resolve_field($field, $table) . ' ' . $direction;
422 }
423
424 /**
425 * Order by author name.
426 *
a4d9adcb
FM
427 * @param bool $firstnamefirst Whether or not the firstname is first in the author's name.
428 * @param string $direction ASC, or DESC.
3bb15852
FM
429 */
430 public function order_by_author($firstnamefirst = false, $direction = '') {
431 $field = self::get_fullname_field($firstnamefirst);
432 $direction = self::normalize_direction($direction);
433 $this->order[] = $field . ' ' . $direction;
434 }
435
436 /**
437 * Convenience method to transform a field into SQL statement.
438 *
439 * @param string $field The field, or *.
440 * @param string $table The table name, without the prefix 'glossary_'.
441 * @return string SQL statement.
442 */
443 protected static function resolve_field($field, $table) {
444 $prefix = constant(__CLASS__ . '::ALIAS_' . core_text::strtoupper($table));
445 return sprintf('%s.%s', $prefix, $field);
446 }
447
448 /**
449 * Simple where conditions.
450 *
451 * @param string $field The field, or *.
452 * @param string $table The table name, without the prefix 'glossary_'.
453 * @param mixed $value The value to be equal to.
454 */
455 public function where($field, $table, $value) {
456 static $i = 0;
457 $sql = self::resolve_field($field, $table) . ' ';
458
459 if ($value === null) {
460 $sql .= 'IS NULL';
461
462 } else {
463 $param = 'where' . $i++;
464 $sql .= " = :$param";
465 $this->params[$param] = $value;
466 }
467
468 $this->where[] = $sql;
469 }
470
471}