[Summary view]

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

   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          $userfieldsapi = \core_user\fields::for_userpic()->including('deleted');
 135          $firstauthorfields = $userfieldsapi->get_sql('fa', false,
 136                  self::FIRST_AUTHOR_ALIAS, self::FIRST_AUTHOR_ID_ALIAS, false)->selects;
 137          $latestuserfields = $userfieldsapi->get_sql('la', false,
 138                  self::LATEST_AUTHOR_ALIAS, self::LATEST_AUTHOR_ID_ALIAS, false)->selects;
 139  
 140          $fields = implode(', ', [
 141              $thistable->get_field_select(),
 142              $posttable->get_field_select(),
 143              $firstauthorfields,
 144              $latestuserfields,
 145          ]);
 146  
 147          $sortkeys = [
 148              $this->get_sort_order(self::SORTORDER_REPLIES_DESC, $includefavourites),
 149              $this->get_sort_order(self::SORTORDER_REPLIES_ASC, $includefavourites)
 150          ];
 151          $issortbyreplies = in_array($sortsql, $sortkeys);
 152  
 153          $tables = $thistable->get_from_sql();
 154          $tables .= ' JOIN ' . $posttable->get_from_sql() . ' ON fp.id = ' . $alias . '.firstpost';
 155          $tables .= ' JOIN {user} fa ON fa.id = fp.userid';
 156          $tables .= ' JOIN {user} la ON la.id = ' . $alias . '.usermodified';
 157          $tables .= $favsql;
 158  
 159          if ($issortbyreplies) {
 160              // Join the discussion replies.
 161              $tables .= ' JOIN (
 162                              SELECT rd.id, COUNT(rp.id) as replycount
 163                              FROM {forum_discussions} rd
 164                              LEFT JOIN {forum_posts} rp
 165                                  ON rp.discussion = rd.id AND rp.id != rd.firstpost
 166                              GROUP BY rd.id
 167                           ) r ON d.id = r.id';
 168          }
 169  
 170          $groupsortorders = [
 171              $this->get_sort_order(self::SORTORDER_GROUP_DESC, $includefavourites),
 172              $this->get_sort_order(self::SORTORDER_GROUP_ASC, $includefavourites)
 173          ];
 174          $sortbygroup = in_array($sortsql, $groupsortorders);
 175          if ($sortbygroup) {
 176              $groupstable = new dml_table('groups', 'g', 'g');
 177              $fields .= ', ' . $groupstable->get_field_select();
 178              // Join groups.
 179              $tables .= 'LEFT JOIN {groups} g ON g.id = d.groupid';
 180          }
 181  
 182          $selectsql = 'SELECT ' . $fields . ' FROM ' . $tables;
 183          $selectsql .= $wheresql ? ' WHERE ' . $wheresql : '';
 184          $selectsql .= $sortsql ? ' ORDER BY ' . $sortsql : '';
 185  
 186          return $selectsql;
 187      }
 188  
 189      /**
 190       * Build the SQL to be used in count_records_sql.
 191       *
 192       * @param string|null $wheresql Where conditions for the SQL
 193       * @return string
 194       */
 195      protected function generate_count_records_sql(string $wheresql = null) : string {
 196          $alias = $this->get_table_alias();
 197          $db = $this->get_db();
 198  
 199          $selectsql = "SELECT COUNT(1) FROM {" . self::TABLE . "} {$alias}";
 200          $selectsql .= $wheresql ? ' WHERE ' . $wheresql : '';
 201  
 202          return $selectsql;
 203      }
 204  
 205      /**
 206       * Get a list of preprocessors to execute on the DB results before being converted
 207       * into entities.
 208       *
 209       * @return array
 210       */
 211      protected function get_preprocessors() : array {
 212          return array_merge(
 213              parent::get_preprocessors(),
 214              [
 215                  'discussion' => new extract_record_preprocessor(self::TABLE, $this->get_table_alias()),
 216                  'firstpost' => new extract_record_preprocessor('forum_posts', 'p_'),
 217                  'firstpostauthor' => new extract_user_preprocessor(self::FIRST_AUTHOR_ID_ALIAS, self::FIRST_AUTHOR_ALIAS),
 218                  'latestpostauthor' => new extract_user_preprocessor(self::LATEST_AUTHOR_ID_ALIAS, self::LATEST_AUTHOR_ALIAS),
 219              ]
 220          );
 221      }
 222  
 223      /**
 224       * Convert the DB records into discussion list entities.
 225       *
 226       * @param array $results The DB records
 227       * @return discussion_list[]
 228       */
 229      protected function from_db_records(array $results) {
 230          $entityfactory = $this->get_entity_factory();
 231  
 232          return array_map(function(array $result) use ($entityfactory) {
 233              [
 234                  'discussion' => $discussion,
 235                  'firstpost' => $firstpost,
 236                  'firstpostauthor' => $firstpostauthor,
 237                  'latestpostauthor' => $latestpostauthor,
 238              ] = $result;
 239              return $entityfactory->get_discussion_summary_from_stdclass(
 240                  $discussion,
 241                  $firstpost,
 242                  $firstpostauthor,
 243                  $latestpostauthor
 244              );
 245          }, $results);
 246      }
 247  
 248      /**
 249       * Get the field to sort by.
 250       *
 251       * @param int|null $sortmethod
 252       * @return string
 253       */
 254      protected function get_keyfield(?int $sortmethod) : string {
 255          global $CFG;
 256  
 257          switch ($sortmethod) {
 258              case self::SORTORDER_CREATED_DESC:
 259              case self::SORTORDER_CREATED_ASC:
 260                  return 'fp.created';
 261              case self::SORTORDER_REPLIES_DESC:
 262              case self::SORTORDER_REPLIES_ASC:
 263                  return 'replycount';
 264              case self::SORTORDER_DISCUSSION_DESC:
 265              case self::SORTORDER_DISCUSSION_ASC:
 266                  return 'dname';
 267              case self::SORTORDER_STARTER_DESC:
 268              case self::SORTORDER_STARTER_ASC:
 269                  // We'll sort by the first name field of the discussion starter's name.
 270  
 271                  // Let's get the full name display config first.
 272                  $nameformat = $CFG->fullnamedisplay;
 273                  if ($CFG->fullnamedisplay === 'language') {
 274                      $nameformat = get_string('fullnamedisplay', '', (object)['firstname' => 'firstname', 'lastname' => 'lastname']);
 275                  }
 276                  // Fetch all the available user name fields.
 277                  $availablefields = order_in_string(\core_user\fields::get_name_fields(), $nameformat);
 278                  // We'll default to the first name if there's no available name field.
 279                  $returnfield = 'firstname';
 280                  if (!empty($availablefields)) {
 281                      // Use the first name field.
 282                      $returnfield = reset($availablefields);
 283                  }
 284                  return 'fauserrecord' . $returnfield;
 285              case self::SORTORDER_GROUP_DESC:
 286              case self::SORTORDER_GROUP_ASC:
 287                  return 'gname';
 288              default:
 289                  global $CFG;
 290                  $alias = $this->get_table_alias();
 291                  $field = "{$alias}.timemodified";
 292                  if (!empty($CFG->forum_enabletimedposts)) {
 293                      return "CASE WHEN {$field} < {$alias}.timestart THEN {$alias}.timestart ELSE {$field} END";
 294                  }
 295                  return $field;
 296          }
 297      }
 298  
 299      /**
 300       * Get the sort direction.
 301       *
 302       * @param int|null $sortmethod
 303       * @return string
 304       */
 305      protected function get_sort_direction(?int $sortmethod) : string {
 306          switch ($sortmethod) {
 307              case self::SORTORDER_LASTPOST_ASC:
 308              case self::SORTORDER_CREATED_ASC:
 309              case self::SORTORDER_REPLIES_ASC:
 310              case self::SORTORDER_DISCUSSION_ASC:
 311              case self::SORTORDER_STARTER_ASC:
 312              case self::SORTORDER_GROUP_ASC:
 313                  return "ASC";
 314              case self::SORTORDER_LASTPOST_DESC:
 315              case self::SORTORDER_CREATED_DESC:
 316              case self::SORTORDER_REPLIES_DESC:
 317              case self::SORTORDER_DISCUSSION_DESC:
 318              case self::SORTORDER_STARTER_DESC:
 319              case self::SORTORDER_GROUP_DESC:
 320              default:
 321                  return "DESC";
 322          }
 323      }
 324  
 325      /**
 326       * Get the sort order SQL for a sort method.
 327       *
 328       * @param int|null  $sortmethod
 329       * @param bool|null $includefavourites
 330       * @return string
 331       */
 332      private function get_sort_order(?int $sortmethod, bool $includefavourites = true) : string {
 333  
 334          $alias = $this->get_table_alias();
 335          // TODO consider user favourites...
 336          $keyfield = $this->get_keyfield($sortmethod);
 337          $direction = $this->get_sort_direction($sortmethod);
 338  
 339          $favouritesort = '';
 340          if ($includefavourites) {
 341              $favalias = $this->get_favourite_alias();
 342              // Since we're joining on the favourite table any discussion that isn't favourited will have
 343              // null in the favourite columns. Nulls behave differently in the sorting for different databases.
 344              // We can ensure consistency between databases by explicitly deprioritising any null favourite field
 345              // using a case statement.
 346              $favouritesort = ", CASE WHEN {$favalias}.id IS NULL THEN 0 ELSE 1 END DESC";
 347              // After the null favourite fields are deprioritised and appear below the favourited discussions we
 348              // need to order the favourited discussions by id so that the most recently favourited discussions
 349              // appear at the top of the list.
 350              $favouritesort .= ", {$favalias}.itemtype DESC";
 351          }
 352  
 353          return "{$alias}.pinned DESC $favouritesort , {$keyfield} {$direction}, {$alias}.id {$direction}";
 354      }
 355  
 356      /**
 357       * Fetch any required SQL to respect timed posts.
 358       *
 359       * @param   bool        $includehiddendiscussions Whether to include hidden discussions or not
 360       * @param   int|null    $includepostsforuser Which user to include posts for, if any
 361       * @return  array       The SQL and parameters to include
 362       */
 363      protected function get_hidden_post_sql(bool $includehiddendiscussions, ?int $includepostsforuser) {
 364          $wheresql = '';
 365          $params = [];
 366          if (!$includehiddendiscussions) {
 367              $now = time();
 368              $wheresql = " AND ((d.timestart <= :timestart AND (d.timeend = 0 OR d.timeend > :timeend))";
 369              $params['timestart'] = $now;
 370              $params['timeend'] = $now;
 371              if (null !== $includepostsforuser) {
 372                  $wheresql .= " OR d.userid = :byuser";
 373                  $params['byuser'] = $includepostsforuser;
 374              }
 375              $wheresql .= ")";
 376          }
 377  
 378          return [
 379              'wheresql' => $wheresql,
 380              'params' => $params,
 381          ];
 382      }
 383  
 384      /**
 385       * Get each discussion, first post, first and last post author for the given forum, considering timed posts, and
 386       * pagination.
 387       *
 388       * @param   int         $forumid The forum to fetch the discussion set for
 389       * @param   bool        $includehiddendiscussions Whether to include hidden discussions or not
 390       * @param   int|null    $includepostsforuser Which user to include posts for, if any
 391       * @param   int         $sortorder The sort order to use
 392       * @param   int         $limit The number of discussions to fetch
 393       * @param   int         $offset The record offset
 394       * @return  array       The set of data fetched
 395       */
 396      public function get_from_forum_id(
 397          int $forumid,
 398          bool $includehiddendiscussions,
 399          ?int $includepostsforuser,
 400          ?int $sortorder,
 401          int $limit,
 402          int $offset
 403      ) {
 404          $alias = $this->get_table_alias();
 405          $wheresql = "{$alias}.forum = :forumid";
 406          [
 407              'wheresql' => $hiddensql,
 408              'params' => $hiddenparams
 409          ] = $this->get_hidden_post_sql($includehiddendiscussions, $includepostsforuser);
 410          $wheresql .= $hiddensql;
 411  
 412          $params = array_merge($hiddenparams, [
 413              'forumid' => $forumid,
 414          ]);
 415  
 416          $includefavourites = $includepostsforuser ? true : false;
 417          $sql = $this->generate_get_records_sql($wheresql, $this->get_sort_order($sortorder, $includefavourites),
 418              $includepostsforuser);
 419          $records = $this->get_db()->get_records_sql($sql, $params, $offset, $limit);
 420  
 421          return $this->transform_db_records_to_entities($records);
 422      }
 423  
 424      /**
 425       * Get each discussion, first post, first and last post author for the given forum, and the set of groups to display
 426       * considering timed posts, and pagination.
 427       *
 428       * @param   int         $forumid The forum to fetch the discussion set for
 429       * @param   int[]       $groupids The list of real groups to filter on
 430       * @param   bool        $includehiddendiscussions Whether to include hidden discussions or not
 431       * @param   int|null    $includepostsforuser Which user to include posts for, if any
 432       * @param   int         $sortorder The sort order to use
 433       * @param   int         $limit The number of discussions to fetch
 434       * @param   int         $offset The record offset
 435       * @return  array       The set of data fetched
 436       */
 437      public function get_from_forum_id_and_group_id(
 438          int $forumid,
 439          array $groupids,
 440          bool $includehiddendiscussions,
 441          ?int $includepostsforuser,
 442          ?int $sortorder,
 443          int $limit,
 444          int $offset
 445      ) {
 446          $alias = $this->get_table_alias();
 447  
 448          $wheresql = "{$alias}.forum = :forumid AND ";
 449          $groupparams = [];
 450          if (empty($groupids)) {
 451              $wheresql .= "{$alias}.groupid = :allgroupsid";
 452          } else {
 453              list($insql, $groupparams) = $this->get_db()->get_in_or_equal($groupids, SQL_PARAMS_NAMED, 'gid');
 454              $wheresql .= "({$alias}.groupid = :allgroupsid OR {$alias}.groupid {$insql})";
 455          }
 456  
 457          [
 458              'wheresql' => $hiddensql,
 459              'params' => $hiddenparams
 460          ] = $this->get_hidden_post_sql($includehiddendiscussions, $includepostsforuser);
 461          $wheresql .= $hiddensql;
 462  
 463          $params = array_merge($hiddenparams, $groupparams, [
 464              'forumid' => $forumid,
 465              'allgroupsid' => -1,
 466          ]);
 467  
 468          $includefavourites = $includepostsforuser ? true : false;
 469          $sql = $this->generate_get_records_sql($wheresql, $this->get_sort_order($sortorder, $includefavourites),
 470              $includepostsforuser);
 471          $records = $this->get_db()->get_records_sql($sql, $params, $offset, $limit);
 472  
 473          return $this->transform_db_records_to_entities($records);
 474      }
 475  
 476      /**
 477       * Count the number of discussions in the forum.
 478       *
 479       * @param int $forumid Id of the forum to count discussions in
 480       * @param bool $includehiddendiscussions Include hidden dicussions in the count?
 481       * @param int|null $includepostsforuser Include discussions created by this user in the count
 482       *                                      (only works if not including hidden discussions).
 483       * @return int
 484       */
 485      public function get_total_discussion_count_from_forum_id(
 486          int $forumid,
 487          bool $includehiddendiscussions,
 488          ?int $includepostsforuser
 489      ) {
 490          $alias = $this->get_table_alias();
 491  
 492          $wheresql = "{$alias}.forum = :forumid";
 493  
 494          [
 495              'wheresql' => $hiddensql,
 496              'params' => $hiddenparams
 497          ] = $this->get_hidden_post_sql($includehiddendiscussions, $includepostsforuser);
 498          $wheresql .= $hiddensql;
 499  
 500          $params = array_merge($hiddenparams, [
 501              'forumid' => $forumid,
 502          ]);
 503  
 504          return $this->get_db()->count_records_sql($this->generate_count_records_sql($wheresql), $params);
 505      }
 506  
 507      /**
 508       * Count the number of discussions in all groups and the list of groups provided.
 509       *
 510       * @param int $forumid Id of the forum to count discussions in
 511       * @param int[] $groupids List of group ids to include in the count (discussions in all groups will always be counted)
 512       * @param bool $includehiddendiscussions Include hidden dicussions in the count?
 513       * @param int|null $includepostsforuser Include discussions created by this user in the count
 514       *                                      (only works if not including hidden discussions).
 515       * @return int
 516       */
 517      public function get_total_discussion_count_from_forum_id_and_group_id(
 518          int $forumid,
 519          array $groupids,
 520          bool $includehiddendiscussions,
 521          ?int $includepostsforuser
 522      ) {
 523          $alias = $this->get_table_alias();
 524  
 525          $wheresql = "{$alias}.forum = :forumid AND ";
 526          $groupparams = [];
 527          if (empty($groupids)) {
 528              $wheresql .= "{$alias}.groupid = :allgroupsid";
 529          } else {
 530              list($insql, $groupparams) = $this->get_db()->get_in_or_equal($groupids, SQL_PARAMS_NAMED, 'gid');
 531              $wheresql .= "({$alias}.groupid = :allgroupsid OR {$alias}.groupid {$insql})";
 532          }
 533  
 534          [
 535              'wheresql' => $hiddensql,
 536              'params' => $hiddenparams
 537          ] = $this->get_hidden_post_sql($includehiddendiscussions, $includepostsforuser);
 538          $wheresql .= $hiddensql;
 539  
 540          $params = array_merge($hiddenparams, $groupparams, [
 541              'forumid' => $forumid,
 542              'allgroupsid' => -1,
 543          ]);
 544  
 545          return $this->get_db()->count_records_sql($this->generate_count_records_sql($wheresql), $params);
 546      }
 547  
 548      /**
 549       * Get the standard favouriting sql.
 550       *
 551       * @param int $userid The ID of the user we are getting the sql for
 552       * @return [$sql, $params] An array comprising of the sql and any associated params
 553       */
 554      private function get_favourite_sql(int $userid): array {
 555  
 556          $usercontext = \context_user::instance($userid);
 557          $alias = $this->get_table_alias();
 558          $ufservice = \core_favourites\service_factory::get_service_for_user_context($usercontext);
 559          list($favsql, $favparams) = $ufservice->get_join_sql_by_type('mod_forum', 'discussions',
 560              $this->get_favourite_alias(), "$alias.id");
 561  
 562          return [$favsql, $favparams];
 563      }
 564  }