[Summary view]

Differences Between: [Versions 310 and 311] [Versions 310 and 400] [Versions 310 and 401] [Versions 310 and 402] [Versions 310 and 403]

   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          $this->fields[] = user_picture::fields('u', null, 'userdataid', 'userdata');
 121      }
 122  
 123      /**
 124       * Internal method to build the query.
 125       *
 126       * @param bool $count Query to count?
 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       *
 155       * @return int The number of records.
 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       *
 165       * @param string $letter     The letter.
 166       * @param string $finalfield The SQL statement representing the field.
 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       *
 182       * @param string $finalfield The SQL statement representing the field.
 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       *
 199       * @param string  $letter         The letter.
 200       * @param bool    $firstnamefirst Whether or not the firstname is first in the author's name.
 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       *
 210       * @param bool $firstnamefirst Whether or not the firstname is first in the author's name.
 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       *
 220       * @param string  $letter         The letter.
 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       *
 238       * @param string $constant One of the NON_APPROVED_* constants.
 239       * @param int    $userid   The user ID when relevant, otherwise current user.
 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  
 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       *
 271       * @param string $term What the concept or aliases should be.
 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  
 281      /**
 282       * Convenience method to get get the SQL statement for the full name.
 283       *
 284       * @param bool $firstnamefirst Whether or not the firstname is first in the author's name.
 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       *
 320       * @param stdClass $record The record.
 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  
 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
 331       * to add a distinct on the entry id.
 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  
 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       *
 347       * @param int $categoryid The category ID, or GLOSSARY_SHOW_* constant.
 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       *
 378       * @param bool $strict When strict uses a JOIN rather than a LEFT JOIN.
 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.
 388       * @param int $from Fetch from.
 389       * @param int $num  Number to fetch.
 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       *
 401       * @param string $direction The desired direction.
 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       *
 427       * @param bool   $firstnamefirst Whether or not the firstname is first in the author's name.
 428       * @param string $direction ASC, or DESC.
 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  }