[Summary view]

Differences Between: [Versions 310 and 401] [Versions 39 and 401]

   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  
  25  defined('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
  40   * @since      Moodle 3.1
  41   */
  42  class mod_glossary_entry_query_builder {
  43  
  44      /** Alias for table glossary_alias. */
  45      const ALIAS_ALIAS = 'ga';
  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       */
  88      public function __construct($glossary = null) {
  89          $this->from = sprintf('FROM {glossary_entries} %s', self::ALIAS_ENTRIES);
  90          if (!empty($glossary)) {
  91              $this->glossary = $glossary;
  92              $this->where[] = sprintf('(%s.glossaryid = :gid OR %s.sourceglossaryid = :gid2)',
  93                  self::ALIAS_ENTRIES, self::ALIAS_ENTRIES);
  94              $this->params['gid'] = $glossary->id;
  95              $this->params['gid2'] = $glossary->id;
  96          }
  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          $userfieldsapi = \core_user\fields::for_userpic();
 121          $fields = $userfieldsapi->get_sql('u', false, 'userdata', '', false)->selects;
 122          $this->fields[] = $fields;
 123      }
 124  
 125      /**
 126       * Internal method to build the query.
 127       *
 128       * @param bool $count Query to count?
 129       * @return string The SQL statement.
 130       */
 131      protected function build_query($count = false) {
 132          $sql = 'SELECT ';
 133  
 134          if ($count) {
 135              $sql .= 'COUNT(\'x\') ';
 136          } else {
 137              $sql .= implode(', ', $this->fields) . ' ';
 138          }
 139  
 140          $sql .= $this->from . ' ';
 141          $sql .= implode(' ', $this->joins) . ' ';
 142  
 143          if (!empty($this->where)) {
 144              $sql .= 'WHERE (' . implode(') AND (', $this->where) . ') ';
 145          }
 146  
 147          if (!$count && !empty($this->order)) {
 148              $sql .= 'ORDER BY ' . implode(', ', $this->order);
 149          }
 150  
 151          return $sql;
 152      }
 153  
 154      /**
 155       * Count the records.
 156       *
 157       * @return int The number of records.
 158       */
 159      public function count_records() {
 160          global $DB;
 161          return $DB->count_records_sql($this->build_query(true), $this->params);
 162      }
 163  
 164      /**
 165       * Filter a field using a letter.
 166       *
 167       * @param string $letter     The letter.
 168       * @param string $finalfield The SQL statement representing the field.
 169       */
 170      protected function filter_by_letter($letter, $finalfield) {
 171          global $DB;
 172  
 173          $letter = core_text::strtoupper($letter);
 174          $len = core_text::strlen($letter);
 175          $sql = $DB->sql_substr(sprintf('upper(%s)', $finalfield), 1, $len);
 176  
 177          $this->where[] = "$sql = :letter";
 178          $this->params['letter'] = $letter;
 179      }
 180  
 181      /**
 182       * Filter a field by special characters.
 183       *
 184       * @param string $finalfield The SQL statement representing the field.
 185       */
 186      protected function filter_by_non_letter($finalfield) {
 187          global $DB;
 188  
 189          $alphabet = explode(',', get_string('alphabet', 'langconfig'));
 190          list($nia, $aparams) = $DB->get_in_or_equal($alphabet, SQL_PARAMS_NAMED, 'nonletter', false);
 191  
 192          $sql = $DB->sql_substr(sprintf('upper(%s)', $finalfield), 1, 1);
 193  
 194          $this->where[] = "$sql $nia";
 195          $this->params = array_merge($this->params, $aparams);
 196      }
 197  
 198      /**
 199       * Filter the author by letter.
 200       *
 201       * @param string  $letter         The letter.
 202       * @param bool    $firstnamefirst Whether or not the firstname is first in the author's name.
 203       */
 204      public function filter_by_author_letter($letter, $firstnamefirst = false) {
 205          $field = self::get_fullname_field($firstnamefirst);
 206          $this->filter_by_letter($letter, $field);
 207      }
 208  
 209      /**
 210       * Filter the author by special characters.
 211       *
 212       * @param bool $firstnamefirst Whether or not the firstname is first in the author's name.
 213       */
 214      public function filter_by_author_non_letter($firstnamefirst = false) {
 215          $field = self::get_fullname_field($firstnamefirst);
 216          $this->filter_by_non_letter($field);
 217      }
 218  
 219      /**
 220       * Filter the concept by letter.
 221       *
 222       * @param string  $letter         The letter.
 223       */
 224      public function filter_by_concept_letter($letter) {
 225          $this->filter_by_letter($letter, self::resolve_field('concept', 'entries'));
 226      }
 227  
 228      /**
 229       * Filter the concept by special characters.
 230       *
 231       * @return void
 232       */
 233      public function filter_by_concept_non_letter() {
 234          $this->filter_by_non_letter(self::resolve_field('concept', 'entries'));
 235      }
 236  
 237      /**
 238       * Filter non approved entries.
 239       *
 240       * @param string $constant One of the NON_APPROVED_* constants.
 241       * @param int    $userid   The user ID when relevant, otherwise current user.
 242       */
 243      public function filter_by_non_approved($constant, $userid = null) {
 244          global $USER;
 245          if (!$userid) {
 246              $userid = $USER->id;
 247          }
 248  
 249          if ($constant === self::NON_APPROVED_ALL) {
 250              // Nothing to do.
 251  
 252          } else if ($constant === self::NON_APPROVED_SELF) {
 253              $this->where[] = sprintf('%s != 0 OR %s = :toapproveuserid',
 254                  self::resolve_field('approved', 'entries'), self::resolve_field('userid', 'entries'));
 255              $this->params['toapproveuserid'] = $USER->id;
 256  
 257          } else if ($constant === self::NON_APPROVED_NONE) {
 258              $this->where[] = sprintf('%s != 0', self::resolve_field('approved', 'entries'));
 259  
 260          } else if ($constant === self::NON_APPROVED_ONLY) {
 261              $this->where[] = sprintf('%s = 0', self::resolve_field('approved', 'entries'));
 262  
 263          } else {
 264              throw new coding_exception('Invalid constant');
 265          }
 266      }
 267  
 268      /**
 269       * Filter by concept or alias.
 270       *
 271       * This requires the alias table to be joined in the query. See {@link self::join_alias()}.
 272       *
 273       * @param string $term What the concept or aliases should be.
 274       */
 275      public function filter_by_term($term) {
 276          $this->where[] = sprintf("(%s = :filterterma OR %s = :filtertermb)",
 277              self::resolve_field('concept', 'entries'),
 278              self::resolve_field('alias', 'alias'));
 279          $this->params['filterterma'] = $term;
 280          $this->params['filtertermb'] = $term;
 281      }
 282  
 283      /**
 284       * Convenience method to get get the SQL statement for the full name.
 285       *
 286       * @param bool $firstnamefirst Whether or not the firstname is first in the author's name.
 287       * @return string The SQL statement.
 288       */
 289      public static function get_fullname_field($firstnamefirst = false) {
 290          global $DB;
 291          if ($firstnamefirst) {
 292              return $DB->sql_fullname(self::resolve_field('firstname', 'user'), self::resolve_field('lastname', 'user'));
 293          }
 294          return $DB->sql_fullname(self::resolve_field('lastname', 'user'), self::resolve_field('firstname', 'user'));
 295      }
 296  
 297      /**
 298       * Get the records.
 299       *
 300       * @return array
 301       */
 302      public function get_records() {
 303          global $DB;
 304          return $DB->get_records_sql($this->build_query(), $this->params, $this->limitfrom, $this->limitnum);
 305      }
 306  
 307      /**
 308       * Get the recordset.
 309       *
 310       * @return moodle_recordset
 311       */
 312      public function get_recordset() {
 313          global $DB;
 314          return $DB->get_recordset_sql($this->build_query(), $this->params, $this->limitfrom, $this->limitnum);
 315      }
 316  
 317      /**
 318       * Retrieve a user object from a record.
 319       *
 320       * This comes handy when {@link self::add_user_fields} was used.
 321       *
 322       * @param stdClass $record The record.
 323       * @return stdClass A user object.
 324       */
 325      public static function get_user_from_record($record) {
 326          return user_picture::unalias($record, null, 'userdataid', 'userdata');
 327      }
 328  
 329      /**
 330       * Join the alias table.
 331       *
 332       * Note that this may cause the same entry to be returned more than once. You might want
 333       * to add a distinct on the entry id.
 334       *
 335       * @return void
 336       */
 337      public function join_alias() {
 338          $this->joins[] = sprintf('LEFT JOIN {glossary_alias} %s ON %s = %s',
 339              self::ALIAS_ALIAS, self::resolve_field('id', 'entries'), self::resolve_field('entryid', 'alias'));
 340      }
 341  
 342      /**
 343       * Join on the category tables.
 344       *
 345       * Depending on the category passed the joins will be different. This is due to the display
 346       * logic that assumes that when displaying all categories the non categorised entries should
 347       * not be returned, etc...
 348       *
 349       * @param int $categoryid The category ID, or GLOSSARY_SHOW_* constant.
 350       */
 351      public function join_category($categoryid) {
 352  
 353          if ($categoryid === GLOSSARY_SHOW_ALL_CATEGORIES) {
 354              $this->joins[] = sprintf('JOIN {glossary_entries_categories} %s ON %s = %s',
 355                  self::ALIAS_ENTRIES_CATEGORIES, self::resolve_field('id', 'entries'),
 356                  self::resolve_field('entryid', 'entries_categories'));
 357  
 358              $this->joins[] = sprintf('JOIN {glossary_categories} %s ON %s = %s',
 359                  self::ALIAS_CATEGORIES, self::resolve_field('id', 'categories'),
 360                  self::resolve_field('categoryid', 'entries_categories'));
 361  
 362          } else if ($categoryid === GLOSSARY_SHOW_NOT_CATEGORISED) {
 363              $this->joins[] = sprintf('LEFT JOIN {glossary_entries_categories} %s ON %s = %s',
 364                  self::ALIAS_ENTRIES_CATEGORIES, self::resolve_field('id', 'entries'),
 365                  self::resolve_field('entryid', 'entries_categories'));
 366  
 367          } else {
 368              $this->joins[] = sprintf('JOIN {glossary_entries_categories} %s ON %s = %s AND %s = :joincategoryid',
 369                  self::ALIAS_ENTRIES_CATEGORIES, self::resolve_field('id', 'entries'),
 370                  self::resolve_field('entryid', 'entries_categories'),
 371                  self::resolve_field('categoryid', 'entries_categories'));
 372              $this->params['joincategoryid'] = $categoryid;
 373  
 374          }
 375      }
 376  
 377      /**
 378       * Join the user table.
 379       *
 380       * @param bool $strict When strict uses a JOIN rather than a LEFT JOIN.
 381       */
 382      public function join_user($strict = false) {
 383          $join = $strict ? 'JOIN' : 'LEFT JOIN';
 384          $this->joins[] = sprintf("$join {user} %s ON %s = %s",
 385              self::ALIAS_USER, self::resolve_field('id', 'user'), self::resolve_field('userid', 'entries'));
 386      }
 387  
 388      /**
 389       * Limit the number of records to fetch.
 390       * @param int $from Fetch from.
 391       * @param int $num  Number to fetch.
 392       */
 393      public function limit($from, $num) {
 394          $this->limitfrom = $from;
 395          $this->limitnum = $num;
 396      }
 397  
 398      /**
 399       * Normalise a direction.
 400       *
 401       * This ensures that the value is either ASC or DESC.
 402       *
 403       * @param string $direction The desired direction.
 404       * @return string ASC or DESC.
 405       */
 406      protected function normalize_direction($direction) {
 407          $direction = core_text::strtoupper($direction);
 408          if ($direction == 'DESC') {
 409              return 'DESC';
 410          }
 411          return 'ASC';
 412      }
 413  
 414      /**
 415       * Order by a field.
 416       *
 417       * @param string $field The field, or *.
 418       * @param string $table The table name, without the prefix 'glossary_'.
 419       * @param string $direction ASC, or DESC.
 420       */
 421      public function order_by($field, $table, $direction = '') {
 422          $direction = self::normalize_direction($direction);
 423          $this->order[] = self::resolve_field($field, $table) . ' ' . $direction;
 424      }
 425  
 426      /**
 427       * Order by author name.
 428       *
 429       * @param bool   $firstnamefirst Whether or not the firstname is first in the author's name.
 430       * @param string $direction ASC, or DESC.
 431       */
 432      public function order_by_author($firstnamefirst = false, $direction = '') {
 433          $field = self::get_fullname_field($firstnamefirst);
 434          $direction = self::normalize_direction($direction);
 435          $this->order[] = $field . ' ' . $direction;
 436      }
 437  
 438      /**
 439       * Convenience method to transform a field into SQL statement.
 440       *
 441       * @param string $field The field, or *.
 442       * @param string $table The table name, without the prefix 'glossary_'.
 443       * @return string SQL statement.
 444       */
 445      protected static function resolve_field($field, $table) {
 446          $prefix = constant(__CLASS__ . '::ALIAS_' . core_text::strtoupper($table));
 447          return sprintf('%s.%s', $prefix, $field);
 448      }
 449  
 450      /**
 451       * Simple where conditions.
 452       *
 453       * @param string $field The field, or *.
 454       * @param string $table The table name, without the prefix 'glossary_'.
 455       * @param mixed $value The value to be equal to.
 456       */
 457      public function where($field, $table, $value) {
 458          static $i = 0;
 459          $sql = self::resolve_field($field, $table) . ' ';
 460  
 461          if ($value === null) {
 462              $sql .= 'IS NULL';
 463  
 464          } else {
 465              $param = 'where' . $i++;
 466              $sql .= " = :$param";
 467              $this->params[$param] = $value;
 468          }
 469  
 470          $this->where[] = $sql;
 471      }
 472  
 473  }