[Summary view]

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

   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   * Vault class for a discussion list.
  19   *
  20   * @package    mod_forum
  21   * @copyright  2019 Andrew Nicols <andrew@nicols.co.uk>
  22   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  23   */
  24  
  25  namespace mod_forum\local\vaults;
  26  
  27  defined('MOODLE_INTERNAL') || die();
  28  
  29  use core_group\output\group_details;
  30  use mod_forum\local\vaults\preprocessors\extract_record as extract_record_preprocessor;
  31  use mod_forum\local\vaults\preprocessors\extract_user as extract_user_preprocessor;
  32  use mod_forum\local\renderers\discussion_list as discussion_list_renderer;
  33  use core\dml\table as dml_table;
  34  use stdClass;
  35  
  36  /**
  37   * Discussion list vault.
  38   *
  39   * This should be the only place that accessed the database.
  40   *
  41   * This uses the repository pattern. See:
  42   * https://designpatternsphp.readthedocs.io/en/latest/More/Repository/README.html
  43   *
  44   * @package    mod_forum
  45   * @copyright  2019 Andrew Nicols <andrew@nicols.co.uk>
  46   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  47   */
  48  class discussion_list extends db_table_vault {
  49      /** The table for this vault */
  50      private const TABLE = 'forum_discussions';
  51      /** Alias for first author id */
  52      private const FIRST_AUTHOR_ID_ALIAS = 'fauserpictureid';
  53      /** Alias for author fields */
  54      private const FIRST_AUTHOR_ALIAS = 'fauserrecord';
  55      /** Alias for last author id */
  56      private const LATEST_AUTHOR_ID_ALIAS = 'lauserpictureid';
  57      /** Alias for last author fields */
  58      private const LATEST_AUTHOR_ALIAS = 'lauserrecord';
  59      /** Default limit */
  60      public const PAGESIZE_DEFAULT = 100;
  61  
  62      /** Sort by newest first */
  63      public const SORTORDER_LASTPOST_DESC = 1;
  64      /** Sort by oldest first */
  65      public const SORTORDER_LASTPOST_ASC = 2;
  66      /** Sort by created desc */
  67      public const SORTORDER_CREATED_DESC = 3;
  68      /** Sort by created asc */
  69      public const SORTORDER_CREATED_ASC = 4;
  70      /** Sort by number of replies desc */
  71      public const SORTORDER_REPLIES_DESC = 5;
  72      /** Sort by number of replies desc */
  73      public const SORTORDER_REPLIES_ASC = 6;
  74      /** Sort by discussion name desc */
  75      public const SORTORDER_DISCUSSION_DESC = 7;
  76      /** Sort by discussion name asc */
  77      public const SORTORDER_DISCUSSION_ASC = 8;
  78      /** Sort by discussion starter's name desc */
  79      public const SORTORDER_STARTER_DESC = 9;
  80      /** Sort by discussion starter's name asc */
  81      public const SORTORDER_STARTER_ASC = 10;
  82      /** Sort by group name desc */
  83      public const SORTORDER_GROUP_DESC = 11;
  84      /** Sort by group name asc */
  85      public const SORTORDER_GROUP_ASC = 12;
  86  
  87      /**
  88       * Get the table alias.
  89       *
  90       * @return string
  91       */
  92      protected function get_table_alias() : string {
  93          return 'd';
  94      }
  95  
  96      /**
  97       * Get the favourite table alias
  98       *
  99       * @return string
 100       */
 101      protected function get_favourite_alias() : string {
 102          return 'favalias';
 103      }
 104  
 105      /**
 106       * Build the SQL to be used in get_records_sql.
 107       *
 108       * @param string|null $wheresql Where conditions for the SQL
 109       * @param string|null $sortsql Order by conditions for the SQL
 110       * @param int|null $userid The ID of the user we are performing this query for
 111       *
 112       * @return string
 113       */
 114      protected function generate_get_records_sql(string $wheresql = null, ?string $sortsql = null, ?int $userid = null) : string {
 115          $alias = $this->get_table_alias();
 116  
 117          $includefavourites = $userid ? true : false;
 118  
 119          $favsql = '';
 120          if ($includefavourites) {
 121              list($favsql, $favparams) = $this->get_favourite_sql($userid);
 122              foreach ($favparams as $key => $param) {
 123                  $favsql = str_replace(":$key", "'$param'", $favsql);
 124              }
 125          }
 126  
 127          // Fetch:
 128          // - Discussion
 129          // - First post
 130          // - Author
 131          // - Most recent editor.
 132          $thistable = new dml_table(self::TABLE, $alias, $alias);
 133          $posttable = new dml_table('forum_posts', 'fp', 'p_');
 134          $firstauthorfields = \user_picture::fields('fa', ['deleted'], self::FIRST_AUTHOR_ID_ALIAS, self::FIRST_AUTHOR_ALIAS);
 135          $latestuserfields = \user_picture::fields('la', ['deleted'], self::LATEST_AUTHOR_ID_ALIAS, self::LATEST_AUTHOR_ALIAS);
 136  
 137          $fields = implode(', ', [
 138              $thistable->get_field_select(),
 139              $posttable->get_field_select(),
 140              $firstauthorfields,
 141              $latestuserfields,
 142          ]);
 143  
 144          $sortkeys = [
 145              $this->get_sort_order(self::SORTORDER_REPLIES_DESC, $includefavourites),
 146              $this->get_sort_order(self::SORTORDER_REPLIES_ASC, $includefavourites)
 147          ];
 148          $issortbyreplies = in_array($sortsql, $sortkeys);
 149  
 150          $tables = $thistable->get_from_sql();
 151          $tables .= ' JOIN ' . $posttable->get_from_sql() . ' ON fp.id = ' . $alias . '.firstpost';
 152          $tables .= ' JOIN {user} fa ON fa.id = fp.userid';
 153          $tables .= ' JOIN {user} la ON la.id = ' . $alias . '.usermodified';
 154          $tables .= $favsql;
 155  
 156          if ($issortbyreplies) {
 157              // Join the discussion replies.
 158              $tables .= ' JOIN (
 159                              SELECT rd.id, COUNT(rp.id) as replycount
 160                              FROM {forum_discussions} rd
 161                              LEFT JOIN {forum_posts} rp
 162                                  ON rp.discussion = rd.id AND rp.id != rd.firstpost
 163                              GROUP BY rd.id
 164                           ) r ON d.id = r.id';
 165          }
 166  
 167          $groupsortorders = [
 168              $this->get_sort_order(self::SORTORDER_GROUP_DESC, $includefavourites),
 169              $this->get_sort_order(self::SORTORDER_GROUP_ASC, $includefavourites)
 170          ];
 171          $sortbygroup = in_array($sortsql, $groupsortorders);
 172          if ($sortbygroup) {
 173              $groupstable = new dml_table('groups', 'g', 'g');
 174              $fields .= ', ' . $groupstable->get_field_select();
 175              // Join groups.
 176              $tables .= 'LEFT JOIN {groups} g ON g.id = d.groupid';
 177          }
 178  
 179          $selectsql = 'SELECT ' . $fields . ' FROM ' . $tables;
 180          $selectsql .= $wheresql ? ' WHERE ' . $wheresql : '';
 181          $selectsql .= $sortsql ? ' ORDER BY ' . $sortsql : '';
 182  
 183          return $selectsql;
 184      }
 185  
 186      /**
 187       * Build the SQL to be used in count_records_sql.
 188       *
 189       * @param string|null $wheresql Where conditions for the SQL
 190       * @return string
 191       */
 192      protected function generate_count_records_sql(string $wheresql = null) : string {
 193          $alias = $this->get_table_alias();
 194          $db = $this->get_db();
 195  
 196          $selectsql = "SELECT COUNT(1) FROM {" . self::TABLE . "} {$alias}";
 197          $selectsql .= $wheresql ? ' WHERE ' . $wheresql : '';
 198  
 199          return $selectsql;
 200      }
 201  
 202      /**
 203       * Get a list of preprocessors to execute on the DB results before being converted
 204       * into entities.
 205       *
 206       * @return array
 207       */
 208      protected function get_preprocessors() : array {
 209          return array_merge(
 210              parent::get_preprocessors(),
 211              [
 212                  'discussion' => new extract_record_preprocessor(self::TABLE, $this->get_table_alias()),
 213                  'firstpost' => new extract_record_preprocessor('forum_posts', 'p_'),
 214                  'firstpostauthor' => new extract_user_preprocessor(self::FIRST_AUTHOR_ID_ALIAS, self::FIRST_AUTHOR_ALIAS),
 215                  'latestpostauthor' => new extract_user_preprocessor(self::LATEST_AUTHOR_ID_ALIAS, self::LATEST_AUTHOR_ALIAS),
 216              ]
 217          );
 218      }
 219  
 220      /**
 221       * Convert the DB records into discussion list entities.
 222       *
 223       * @param array $results The DB records
 224       * @return discussion_list[]
 225       */
 226      protected function from_db_records(array $results) {
 227          $entityfactory = $this->get_entity_factory();
 228  
 229          return array_map(function(array $result) use ($entityfactory) {
 230              [
 231                  'discussion' => $discussion,
 232                  'firstpost' => $firstpost,
 233                  'firstpostauthor' => $firstpostauthor,
 234                  'latestpostauthor' => $latestpostauthor,
 235              ] = $result;
 236              return $entityfactory->get_discussion_summary_from_stdclass(
 237                  $discussion,
 238                  $firstpost,
 239                  $firstpostauthor,
 240                  $latestpostauthor
 241              );
 242          }, $results);
 243      }
 244  
 245      /**
 246       * Get the field to sort by.
 247       *
 248       * @param int|null $sortmethod
 249       * @return string
 250       */
 251      protected function get_keyfield(?int $sortmethod) : string {
 252          global $CFG;
 253  
 254          switch ($sortmethod) {
 255              case self::SORTORDER_CREATED_DESC:
 256              case self::SORTORDER_CREATED_ASC:
 257                  return 'fp.created';
 258              case self::SORTORDER_REPLIES_DESC:
 259              case self::SORTORDER_REPLIES_ASC:
 260                  return 'replycount';
 261              case self::SORTORDER_DISCUSSION_DESC:
 262              case self::SORTORDER_DISCUSSION_ASC:
 263                  return 'dname';
 264              case self::SORTORDER_STARTER_DESC:
 265              case self::SORTORDER_STARTER_ASC:
 266                  // We'll sort by the first name field of the discussion starter's name.
 267  
 268                  // Let's get the full name display config first.
 269                  $nameformat = $CFG->fullnamedisplay;
 270                  if ($CFG->fullnamedisplay === 'language') {
 271                      $nameformat = get_string('fullnamedisplay', '', (object)['firstname' => 'firstname', 'lastname' => 'lastname']);
 272                  }
 273                  // Fetch all the available user name fields.
 274                  $availablefields = order_in_string(get_all_user_name_fields(), $nameformat);
 275                  // We'll default to the first name if there's no available name field.
 276                  $returnfield = 'firstname';
 277                  if (!empty($availablefields)) {
 278                      // Use the first name field.
 279                      $returnfield = reset($availablefields);
 280                  }
 281                  return 'fauserrecord' . $returnfield;
 282              case self::SORTORDER_GROUP_DESC:
 283              case self::SORTORDER_GROUP_ASC:
 284                  return 'gname';
 285              default:
 286                  global $CFG;
 287                  $alias = $this->get_table_alias();
 288                  $field = "{$alias}.timemodified";
 289                  if (!empty($CFG->forum_enabletimedposts)) {
 290                      return "CASE WHEN {$field} < {$alias}.timestart THEN {$alias}.timestart ELSE {$field} END";
 291                  }
 292                  return $field;
 293          }
 294      }
 295  
 296      /**
 297       * Get the sort direction.
 298       *
 299       * @param int|null $sortmethod
 300       * @return string
 301       */
 302      protected function get_sort_direction(?int $sortmethod) : string {
 303          switch ($sortmethod) {
 304              case self::SORTORDER_LASTPOST_ASC:
 305              case self::SORTORDER_CREATED_ASC:
 306              case self::SORTORDER_REPLIES_ASC:
 307              case self::SORTORDER_DISCUSSION_ASC:
 308              case self::SORTORDER_STARTER_ASC:
 309              case self::SORTORDER_GROUP_ASC:
 310                  return "ASC";
 311              case self::SORTORDER_LASTPOST_DESC:
 312              case self::SORTORDER_CREATED_DESC:
 313              case self::SORTORDER_REPLIES_DESC:
 314              case self::SORTORDER_DISCUSSION_DESC:
 315              case self::SORTORDER_STARTER_DESC:
 316              case self::SORTORDER_GROUP_DESC:
 317              default:
 318                  return "DESC";
 319          }
 320      }
 321  
 322      /**
 323       * Get the sort order SQL for a sort method.
 324       *
 325       * @param int|null  $sortmethod
 326       * @param bool|null $includefavourites
 327       * @return string
 328       */
 329      private function get_sort_order(?int $sortmethod, bool $includefavourites = true) : string {
 330  
 331          $alias = $this->get_table_alias();
 332          // TODO consider user favourites...
 333          $keyfield = $this->get_keyfield($sortmethod);
 334          $direction = $this->get_sort_direction($sortmethod);
 335  
 336          $favouritesort = '';
 337          if ($includefavourites) {
 338              $favalias = $this->get_favourite_alias();
 339              // Since we're joining on the favourite table any discussion that isn't favourited will have
 340              // null in the favourite columns. Nulls behave differently in the sorting for different databases.
 341              // We can ensure consistency between databases by explicitly deprioritising any null favourite field
 342              // using a case statement.
 343              $favouritesort = ", CASE WHEN {$favalias}.id IS NULL THEN 0 ELSE 1 END DESC";
 344              // After the null favourite fields are deprioritised and appear below the favourited discussions we
 345              // need to order the favourited discussions by id so that the most recently favourited discussions
 346              // appear at the top of the list.
 347              $favouritesort .= ", {$favalias}.itemtype DESC";
 348          }
 349  
 350          return "{$alias}.pinned DESC $favouritesort , {$keyfield} {$direction}";
 351      }
 352  
 353      /**
 354       * Fetch any required SQL to respect timed posts.
 355       *
 356       * @param   bool        $includehiddendiscussions Whether to include hidden discussions or not
 357       * @param   int|null    $includepostsforuser Which user to include posts for, if any
 358       * @return  array       The SQL and parameters to include
 359       */
 360      protected function get_hidden_post_sql(bool $includehiddendiscussions, ?int $includepostsforuser) {
 361          $wheresql = '';
 362          $params = [];
 363          if (!$includehiddendiscussions) {
 364              $now = time();
 365              $wheresql = " AND ((d.timestart <= :timestart AND (d.timeend = 0 OR d.timeend > :timeend))";
 366              $params['timestart'] = $now;
 367              $params['timeend'] = $now;
 368              if (null !== $includepostsforuser) {
 369                  $wheresql .= " OR d.userid = :byuser";
 370                  $params['byuser'] = $includepostsforuser;
 371              }
 372              $wheresql .= ")";
 373          }
 374  
 375          return [
 376              'wheresql' => $wheresql,
 377              'params' => $params,
 378          ];
 379      }
 380  
 381      /**
 382       * Get each discussion, first post, first and last post author for the given forum, considering timed posts, and
 383       * pagination.
 384       *
 385       * @param   int         $forumid The forum to fetch the discussion set for
 386       * @param   bool        $includehiddendiscussions Whether to include hidden discussions or not
 387       * @param   int|null    $includepostsforuser Which user to include posts for, if any
 388       * @param   int         $sortorder The sort order to use
 389       * @param   int         $limit The number of discussions to fetch
 390       * @param   int         $offset The record offset
 391       * @return  array       The set of data fetched
 392       */
 393      public function get_from_forum_id(
 394          int $forumid,
 395          bool $includehiddendiscussions,
 396          ?int $includepostsforuser,
 397          ?int $sortorder,
 398          int $limit,
 399          int $offset
 400      ) {
 401          $alias = $this->get_table_alias();
 402          $wheresql = "{$alias}.forum = :forumid";
 403          [
 404              'wheresql' => $hiddensql,
 405              'params' => $hiddenparams
 406          ] = $this->get_hidden_post_sql($includehiddendiscussions, $includepostsforuser);
 407          $wheresql .= $hiddensql;
 408  
 409          $params = array_merge($hiddenparams, [
 410              'forumid' => $forumid,
 411          ]);
 412  
 413          $includefavourites = $includepostsforuser ? true : false;
 414          $sql = $this->generate_get_records_sql($wheresql, $this->get_sort_order($sortorder, $includefavourites),
 415              $includepostsforuser);
 416          $records = $this->get_db()->get_records_sql($sql, $params, $offset, $limit);
 417  
 418          return $this->transform_db_records_to_entities($records);
 419      }
 420  
 421      /**
 422       * Get each discussion, first post, first and last post author for the given forum, and the set of groups to display
 423       * considering timed posts, and pagination.
 424       *
 425       * @param   int         $forumid The forum to fetch the discussion set for
 426       * @param   int[]       $groupids The list of real groups to filter on
 427       * @param   bool        $includehiddendiscussions Whether to include hidden discussions or not
 428       * @param   int|null    $includepostsforuser Which user to include posts for, if any
 429       * @param   int         $sortorder The sort order to use
 430       * @param   int         $limit The number of discussions to fetch
 431       * @param   int         $offset The record offset
 432       * @return  array       The set of data fetched
 433       */
 434      public function get_from_forum_id_and_group_id(
 435          int $forumid,
 436          array $groupids,
 437          bool $includehiddendiscussions,
 438          ?int $includepostsforuser,
 439          ?int $sortorder,
 440          int $limit,
 441          int $offset
 442      ) {
 443          $alias = $this->get_table_alias();
 444  
 445          $wheresql = "{$alias}.forum = :forumid AND ";
 446          $groupparams = [];
 447          if (empty($groupids)) {
 448              $wheresql .= "{$alias}.groupid = :allgroupsid";
 449          } else {
 450              list($insql, $groupparams) = $this->get_db()->get_in_or_equal($groupids, SQL_PARAMS_NAMED, 'gid');
 451              $wheresql .= "({$alias}.groupid = :allgroupsid OR {$alias}.groupid {$insql})";
 452          }
 453  
 454          [
 455              'wheresql' => $hiddensql,
 456              'params' => $hiddenparams
 457          ] = $this->get_hidden_post_sql($includehiddendiscussions, $includepostsforuser);
 458          $wheresql .= $hiddensql;
 459  
 460          $params = array_merge($hiddenparams, $groupparams, [
 461              'forumid' => $forumid,
 462              'allgroupsid' => -1,
 463          ]);
 464  
 465          $includefavourites = $includepostsforuser ? true : false;
 466          $sql = $this->generate_get_records_sql($wheresql, $this->get_sort_order($sortorder, $includefavourites),
 467              $includepostsforuser);
 468          $records = $this->get_db()->get_records_sql($sql, $params, $offset, $limit);
 469  
 470          return $this->transform_db_records_to_entities($records);
 471      }
 472  
 473      /**
 474       * Count the number of discussions in the forum.
 475       *
 476       * @param int $forumid Id of the forum to count discussions in
 477       * @param bool $includehiddendiscussions Include hidden dicussions in the count?
 478       * @param int|null $includepostsforuser Include discussions created by this user in the count
 479       *                                      (only works if not including hidden discussions).
 480       * @return int
 481       */
 482      public function get_total_discussion_count_from_forum_id(
 483          int $forumid,
 484          bool $includehiddendiscussions,
 485          ?int $includepostsforuser
 486      ) {
 487          $alias = $this->get_table_alias();
 488  
 489          $wheresql = "{$alias}.forum = :forumid";
 490  
 491          [
 492              'wheresql' => $hiddensql,
 493              'params' => $hiddenparams
 494          ] = $this->get_hidden_post_sql($includehiddendiscussions, $includepostsforuser);
 495          $wheresql .= $hiddensql;
 496  
 497          $params = array_merge($hiddenparams, [
 498              'forumid' => $forumid,
 499          ]);
 500  
 501          return $this->get_db()->count_records_sql($this->generate_count_records_sql($wheresql), $params);
 502      }
 503  
 504      /**
 505       * Count the number of discussions in all groups and the list of groups provided.
 506       *
 507       * @param int $forumid Id of the forum to count discussions in
 508       * @param int[] $groupids List of group ids to include in the count (discussions in all groups will always be counted)
 509       * @param bool $includehiddendiscussions Include hidden dicussions in the count?
 510       * @param int|null $includepostsforuser Include discussions created by this user in the count
 511       *                                      (only works if not including hidden discussions).
 512       * @return int
 513       */
 514      public function get_total_discussion_count_from_forum_id_and_group_id(
 515          int $forumid,
 516          array $groupids,
 517          bool $includehiddendiscussions,
 518          ?int $includepostsforuser
 519      ) {
 520          $alias = $this->get_table_alias();
 521  
 522          $wheresql = "{$alias}.forum = :forumid AND ";
 523          $groupparams = [];
 524          if (empty($groupids)) {
 525              $wheresql .= "{$alias}.groupid = :allgroupsid";
 526          } else {
 527              list($insql, $groupparams) = $this->get_db()->get_in_or_equal($groupids, SQL_PARAMS_NAMED, 'gid');
 528              $wheresql .= "({$alias}.groupid = :allgroupsid OR {$alias}.groupid {$insql})";
 529          }
 530  
 531          [
 532              'wheresql' => $hiddensql,
 533              'params' => $hiddenparams
 534          ] = $this->get_hidden_post_sql($includehiddendiscussions, $includepostsforuser);
 535          $wheresql .= $hiddensql;
 536  
 537          $params = array_merge($hiddenparams, $groupparams, [
 538              'forumid' => $forumid,
 539              'allgroupsid' => -1,
 540          ]);
 541  
 542          return $this->get_db()->count_records_sql($this->generate_count_records_sql($wheresql), $params);
 543      }
 544  
 545      /**
 546       * Get the standard favouriting sql.
 547       *
 548       * @param int $userid The ID of the user we are getting the sql for
 549       * @return [$sql, $params] An array comprising of the sql and any associated params
 550       */
 551      private function get_favourite_sql(int $userid): array {
 552  
 553          $usercontext = \context_user::instance($userid);
 554          $alias = $this->get_table_alias();
 555          $ufservice = \core_favourites\service_factory::get_service_for_user_context($usercontext);
 556          list($favsql, $favparams) = $ufservice->get_join_sql_by_type('mod_forum', 'discussions',
 557              $this->get_favourite_alias(), "$alias.id");
 558  
 559          return [$favsql, $favparams];
 560      }
 561  }