[Summary view]

   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   * Post vault class.
  19   *
  20   * @package    mod_forum
  21   * @copyright  2019 Ryan Wyllie <ryan@moodle.com>
  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 mod_forum\local\entities\forum as forum_entity;
  30  use mod_forum\local\entities\post as post_entity;
  31  use mod_forum\local\factories\entity as entity_factory;
  32  use stdClass;
  33  
  34  /**
  35   * Post vault class.
  36   *
  37   * This should be the only place that accessed the database.
  38   *
  39   * This class should not return any objects other than post_entity objects. The class
  40   * may contain some utility count methods which return integers.
  41   *
  42   * This uses the repository pattern. See:
  43   * https://designpatternsphp.readthedocs.io/en/latest/More/Repository/README.html
  44   *
  45   * @copyright  2019 Ryan Wyllie <ryan@moodle.com>
  46   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  47   */
  48  class post extends db_table_vault {
  49      /** The table for this vault */
  50      private const TABLE = 'forum_posts';
  51      /** Alias for user id */
  52      private const USER_ID_ALIAS = 'userpictureid';
  53      /** Alias for user fields */
  54      private const USER_ALIAS = 'userrecord';
  55  
  56      /**
  57       * Get the table alias.
  58       *
  59       * @return string
  60       */
  61      protected function get_table_alias() : string {
  62          return 'p';
  63      }
  64  
  65      /**
  66       * Build the SQL to be used in get_records_sql.
  67       *
  68       * @param string|null $wheresql Where conditions for the SQL
  69       * @param string|null $sortsql Order by conditions for the SQL
  70       * @param int|null $userid The user ID
  71       * @return string
  72       */
  73      protected function generate_get_records_sql(string $wheresql = null, string $sortsql = null, ?int $userid = null) : string {
  74          $table = self::TABLE;
  75          $alias = $this->get_table_alias();
  76          $fields = $alias . '.*';
  77          $tables = "{{$table}} {$alias}";
  78  
  79          $selectsql = "SELECT {$fields} FROM {$tables}";
  80          $selectsql .= $wheresql ? ' WHERE ' . $wheresql : '';
  81          $selectsql .= $sortsql ? ' ORDER BY ' . $sortsql : '';
  82  
  83          return $selectsql;
  84      }
  85  
  86      /**
  87       * Convert the DB records into post entities.
  88       *
  89       * @param array $results The DB records
  90       * @return post_entity[]
  91       */
  92      protected function from_db_records(array $results) {
  93          $entityfactory = $this->get_entity_factory();
  94  
  95          return array_map(function(array $result) use ($entityfactory) {
  96              ['record' => $record] = $result;
  97              return $entityfactory->get_post_from_stdclass($record);
  98          }, $results);
  99      }
 100  
 101      /**
 102       * Get the post ids for the given discussion.
 103       *
 104       * @param stdClass $user The user to check the unread count for
 105       * @param int $discussionid The discussion to load posts for
 106       * @param bool $canseeprivatereplies Whether this user can see all private replies or not
 107       * @param string $orderby Order the results
 108       * @return post_entity[]
 109       */
 110      public function get_from_discussion_id(
 111          stdClass $user,
 112          int $discussionid,
 113          bool $canseeprivatereplies,
 114          string $orderby = 'created ASC'
 115      ) : array {
 116          return $this->get_from_discussion_ids($user, [$discussionid], $canseeprivatereplies, $orderby);
 117      }
 118  
 119      /**
 120       * Get the list of posts for the given discussions.
 121       *
 122       * @param stdClass $user The user to load posts for.
 123       * @param int[] $discussionids The list of discussion ids to load posts for
 124       * @param bool $canseeprivatereplies Whether this user can see all private replies or not
 125       * @param string $orderby Order the results
 126       * @return post_entity[]
 127       */
 128      public function get_from_discussion_ids(
 129          stdClass $user,
 130          array $discussionids,
 131          bool $canseeprivatereplies,
 132          string $orderby = ''
 133      ) : array {
 134          if (empty($discussionids)) {
 135              return [];
 136          }
 137  
 138          return $this->get_from_filters($user, ['discussionids' => $discussionids], $canseeprivatereplies, $orderby);
 139      }
 140  
 141      /**
 142       * The method returns posts based on a set of filters.
 143       *
 144       * @param stdClass $user Only used when restricting private replies
 145       * @param array $filters Export filters, valid filters are:
 146       *
 147       * 'discussionids' => array of discussion ids eg [1,2,3]
 148       * 'userids' => array of user ids eg [1,2,3]
 149       * 'from' => timestamp to filter posts from this date.
 150       *  'to' => timestamp to filter posts till this date.
 151       *
 152       * @param bool $canseeprivatereplies Whether this user can see all private replies or not
 153       * @param string $orderby Order the results
 154       * @return post_entity[]
 155       */
 156      public function get_from_filters(
 157              stdClass $user,
 158              array $filters,
 159              bool $canseeprivatereplies,
 160              string $orderby = ''
 161      ): array {
 162          if (count($filters) == 0) {
 163              return [];
 164          }
 165          $wheresql = [];
 166          $params = [];
 167          $alias = $this->get_table_alias();
 168  
 169          // Filter by discussion ids.
 170          if (!empty($filters['discussionids'])) {
 171              list($indiscussionssql, $indiscussionsparams) = $this->get_db()->get_in_or_equal($filters['discussionids'],
 172                  SQL_PARAMS_NAMED);
 173              $wheresql[] = "{$alias}.discussion {$indiscussionssql}";
 174              $params += $indiscussionsparams;
 175          }
 176  
 177          // Filter by user ids.
 178          if (!empty($filters['userids'])) {
 179              list($inuserssql, $inusersparams) = $this->get_db()->get_in_or_equal($filters['userids'],
 180                  SQL_PARAMS_NAMED);
 181              $wheresql[] = "{$alias}.userid {$inuserssql}";
 182              $params += $inusersparams;
 183          }
 184  
 185          // Filter posts by from and to dates.
 186          if (isset($filters['from'])) {
 187              $wheresql[] = "{$alias}.created >= :from";
 188              $params['from'] = $filters['from'];
 189          }
 190  
 191          if (isset($filters['to'])) {
 192              $wheresql[] = "{$alias}.created < :to";
 193              $params['to'] = $filters['to'];
 194          }
 195  
 196          // We need to build the WHERE here, because get_private_reply_sql returns the query with the AND clause.
 197          $wheresql = implode(' AND ', $wheresql);
 198  
 199          // Build private replies sql.
 200          [
 201              'where' => $privatewhere,
 202              'params' => $privateparams,
 203          ] = $this->get_private_reply_sql($user, $canseeprivatereplies);
 204          $wheresql .= "{$privatewhere}";
 205          $params += $privateparams;
 206  
 207          if ($orderby) {
 208              $orderbysql = $alias . '.' . $orderby;
 209          } else {
 210              $orderbysql = '';
 211          }
 212  
 213          $sql = $this->generate_get_records_sql($wheresql, $orderbysql);
 214          $records = $this->get_db()->get_records_sql($sql, $params);
 215  
 216          return $this->transform_db_records_to_entities($records);
 217      }
 218  
 219      /**
 220       * Load a list of replies to the given post. This will load all descendants of the post.
 221       * That is, all direct replies and replies to those replies etc.
 222       *
 223       * The return value will be a flat array of posts in the requested order.
 224       *
 225       * @param stdClass    $user The user to check the unread count for
 226       * @param post_entity $post The post to load replies for
 227       * @param bool        $canseeprivatereplies Whether this user can see all private replies or not
 228       * @param string $orderby How to order the replies
 229       * @return post_entity[]
 230       */
 231      public function get_replies_to_post(
 232          stdClass $user,
 233          post_entity $post,
 234          bool $canseeprivatereplies,
 235          string $orderby = 'created ASC'
 236      ) : array {
 237          $alias = $this->get_table_alias();
 238  
 239          [
 240              'where' => $privatewhere,
 241              'params' => $privateparams,
 242          ] = $this->get_private_reply_sql($user, $canseeprivatereplies);
 243  
 244          $params = array_merge([
 245              'discussionid' => $post->get_discussion_id(),
 246              'created' => $post->get_time_created(),
 247              'excludepostid' => $post->get_id(),
 248          ], $privateparams);
 249  
 250          // Unfortunately the best we can do to filter down the query is ignore all posts
 251          // that were created before the given post (since they can't be replies).
 252          // We also filter to remove private replies if the user cannot vie them.
 253          $wheresql = "{$alias}.discussion = :discussionid
 254                   AND {$alias}.created >= :created {$privatewhere}
 255                   AND {$alias}.id != :excludepostid";
 256          $orderbysql = $alias . '.' . $orderby;
 257          $sql = $this->generate_get_records_sql($wheresql, $orderbysql);
 258          $records = $this->get_db()->get_records_sql($sql, $params);
 259          $posts = $this->transform_db_records_to_entities($records);
 260          $sorter = $this->get_entity_factory()->get_posts_sorter();
 261  
 262          // We need to sort all of the values into the replies tree in order to capture
 263          // the full list of descendants.
 264          $sortedposts = $sorter->sort_into_children($posts);
 265          $replies = [];
 266  
 267          // From the sorted list we can grab the first elements and check if they are replies
 268          // to the post we care about. If so we keep them.
 269          foreach ($sortedposts as $candidate) {
 270              [$candidatepost, $candidatereplies] = $candidate;
 271              if ($candidatepost->has_parent() && $candidatepost->get_parent_id() == $post->get_id()) {
 272                  $replies[] = $candidate;
 273              }
 274          }
 275  
 276          if (empty($replies)) {
 277              return $replies;
 278          }
 279  
 280          $getreplypostids = function($candidates) use (&$getreplypostids) {
 281              $ids = [];
 282  
 283              foreach ($candidates as $candidate) {
 284                  [$reply, $replies] = $candidate;
 285                  $ids = array_merge($ids, [$reply->get_id()], $getreplypostids($replies));
 286              }
 287  
 288              return $ids;
 289          };
 290          // Recursively build a list of the ids of all posts in the full reply tree.
 291          $replypostids = $getreplypostids($replies);
 292  
 293          // Now go back and filter the original result set down to just the posts that
 294          // we've flagged as in the reply tree. We need to filter the original set of values
 295          // so that we can maintain the requested sort order.
 296          return array_values(array_filter($posts, function($post) use ($replypostids) {
 297              return in_array($post->get_id(), $replypostids);
 298          }));
 299      }
 300  
 301      /**
 302       * Get a mapping of replies to the specified discussions.
 303       *
 304       * @param   stdClass    $user The user to check the unread count for
 305       * @param   int[]       $discussionids The list of discussions to fetch counts for
 306       * @param   bool        $canseeprivatereplies Whether this user can see all private replies or not
 307       * @return  int[]       The number of replies for each discussion returned in an associative array
 308       */
 309      public function get_reply_count_for_discussion_ids(stdClass $user, array $discussionids, bool $canseeprivatereplies) : array {
 310          if (empty($discussionids)) {
 311              return [];
 312          }
 313  
 314          list($insql, $params) = $this->get_db()->get_in_or_equal($discussionids, SQL_PARAMS_NAMED);
 315  
 316          [
 317              'where' => $privatewhere,
 318              'params' => $privateparams,
 319          ] = $this->get_private_reply_sql($user, $canseeprivatereplies);
 320  
 321          $sql = "SELECT discussion, COUNT(1)
 322                    FROM {" . self::TABLE . "} p
 323                   WHERE p.discussion {$insql} AND p.parent > 0 {$privatewhere}
 324                   GROUP BY discussion";
 325  
 326          return $this->get_db()->get_records_sql_menu($sql, array_merge($params, $privateparams));
 327      }
 328  
 329      /**
 330       * Get a mapping of replies to the specified discussions.
 331       *
 332       * @param   stdClass    $user The user to check the unread count for
 333       * @param   int         $postid The post to collect replies to
 334       * @param   int         $discussionid The list of discussions to fetch counts for
 335       * @param   bool        $canseeprivatereplies Whether this user can see all private replies or not
 336       * @return  int         The number of replies for each discussion returned in an associative array
 337       */
 338      public function get_reply_count_for_post_id_in_discussion_id(
 339              stdClass $user, int $postid, int $discussionid, bool $canseeprivatereplies) : int {
 340          [
 341              'where' => $privatewhere,
 342              'params' => $privateparams,
 343          ] = $this->get_private_reply_sql($user, $canseeprivatereplies);
 344  
 345          $alias = $this->get_table_alias();
 346          $table = self::TABLE;
 347  
 348          $sql = "SELECT {$alias}.id, {$alias}.parent
 349                    FROM {{$table}} {$alias}
 350                   WHERE p.discussion = :discussionid {$privatewhere}";
 351  
 352          $postparents = $this->get_db()->get_records_sql_menu($sql, array_merge([
 353                  'discussionid' => $discussionid,
 354              ], $privateparams));
 355  
 356          return $this->count_children_from_parent_recursively($postparents, $postid);
 357      }
 358  
 359      /**
 360       * Count the children whose parent matches the current record recursively.
 361       *
 362       * @param   array   $postparents The full mapping of posts.
 363       * @param   int     $postid The ID to check for
 364       * @return  int     $count
 365       */
 366      private function count_children_from_parent_recursively(array $postparents, int $postid) : int {
 367          if (!isset($postparents[$postid])) {
 368              // Post not found at all.
 369              return 0;
 370          }
 371  
 372          $count = 0;
 373          foreach ($postparents as $pid => $parentid) {
 374              if ($postid == $parentid) {
 375                  $count += $this->count_children_from_parent_recursively($postparents, $pid) + 1;
 376              }
 377          }
 378  
 379          return $count;
 380      }
 381  
 382      /**
 383       * Get a mapping of unread post counts for the specified discussions.
 384       *
 385       * @param   stdClass    $user The user to fetch counts for
 386       * @param   int[]       $discussionids The list of discussions to fetch counts for
 387       * @param   bool        $canseeprivatereplies Whether this user can see all private replies or not
 388       * @return  int[]       The count of unread posts for each discussion returned in an associative array
 389       */
 390      public function get_unread_count_for_discussion_ids(stdClass $user, array $discussionids, bool $canseeprivatereplies) : array {
 391          global $CFG;
 392  
 393          if (empty($discussionids)) {
 394              return [];
 395          }
 396  
 397          [
 398              'where' => $privatewhere,
 399              'params' => $privateparams,
 400          ] = $this->get_private_reply_sql($user, $canseeprivatereplies);
 401  
 402          $alias = $this->get_table_alias();
 403          list($insql, $params) = $this->get_db()->get_in_or_equal($discussionids, SQL_PARAMS_NAMED);
 404          $sql = "SELECT p.discussion, COUNT(p.id) FROM {" . self::TABLE . "} p
 405               LEFT JOIN {forum_read} r ON r.postid = p.id AND r.userid = :userid
 406                   WHERE p.discussion {$insql} AND p.modified > :cutofftime AND r.id IS NULL {$privatewhere}
 407                GROUP BY p.discussion";
 408  
 409          $params['userid'] = $user->id;
 410          $params['cutofftime'] = floor((new \DateTime())
 411              ->sub(new \DateInterval("P{$CFG->forum_oldpostdays}D"))
 412              ->format('U') / 60) * 60;
 413  
 414          return $this->get_db()->get_records_sql_menu($sql, array_merge($params, $privateparams));
 415      }
 416  
 417      /**
 418       * Get a mapping of the most recent post record in each discussion based on post creation time.
 419       *
 420       * @param stdClass $user
 421       * @param array $discussionids
 422       * @param bool $canseeprivatereplies
 423       * @return array
 424       * @throws \coding_exception
 425       * @throws \dml_exception
 426       */
 427      public function get_latest_posts_for_discussion_ids(
 428          stdClass $user, array $discussionids, bool $canseeprivatereplies) : array {
 429  
 430          if (empty($discussionids)) {
 431              return [];
 432          }
 433  
 434          list($insql, $params) = $this->get_db()->get_in_or_equal($discussionids, SQL_PARAMS_NAMED);
 435  
 436          [
 437              'where' => $privatewhere,
 438              'params' => $privateparams,
 439          ] = $this->get_private_reply_sql($user, $canseeprivatereplies, "mp");
 440  
 441          $sql = "
 442              SELECT posts.*
 443              FROM {" . self::TABLE . "} posts
 444              JOIN (
 445                  SELECT p.discussion, MAX(p.id) as latestpostid
 446                  FROM {" . self::TABLE . "} p
 447                  JOIN (
 448                      SELECT mp.discussion, MAX(mp.created) AS created
 449                        FROM {" . self::TABLE . "} mp
 450                       WHERE mp.discussion {$insql} {$privatewhere}
 451                    GROUP BY mp.discussion
 452                  ) lp ON lp.discussion = p.discussion AND lp.created = p.created
 453              GROUP BY p.discussion
 454            ) plp on plp.discussion = posts.discussion AND plp.latestpostid = posts.id";
 455  
 456          $records = $this->get_db()->get_records_sql($sql, array_merge($params, $privateparams));
 457          $entities = $this->transform_db_records_to_entities($records);
 458  
 459          return array_reduce($entities, function($carry, $entity) {
 460              $carry[$entity->get_discussion_id()] = $entity;
 461              return $carry;
 462          }, []);
 463      }
 464  
 465      /**
 466       * Get the SQL where and additional parameters to use to restrict posts to private reply posts.
 467       *
 468       * @param   stdClass    $user The user to fetch counts for
 469       * @param   bool        $canseeprivatereplies Whether this user can see all private replies or not
 470       * @return  array       The SQL WHERE clause, and parameters to use in the SQL.
 471       */
 472      private function get_private_reply_sql(stdClass $user, bool $canseeprivatereplies, $posttablealias = "p") {
 473          $params = [];
 474          $privatewhere = '';
 475          if (!$canseeprivatereplies) {
 476              $privatewhere = " AND ({$posttablealias}.privatereplyto = :privatereplyto OR " .
 477                  "{$posttablealias}.userid = :privatereplyfrom OR {$posttablealias}.privatereplyto = 0)";
 478              $params['privatereplyto'] = $user->id;
 479              $params['privatereplyfrom'] = $user->id;
 480          }
 481  
 482          return [
 483              'where' => $privatewhere,
 484              'params' => $params,
 485          ];
 486      }
 487  
 488      /**
 489       * Get a mapping of the first post in each discussion based on post creation time.
 490       *
 491       * @param   int[]         $discussionids The list of discussions to fetch counts for
 492       * @return  post_entity[] The post object of the first post for each discussions returned in an associative array
 493       */
 494      public function get_first_post_for_discussion_ids(array $discussionids) : array {
 495  
 496          if (empty($discussionids)) {
 497              return [];
 498          }
 499  
 500          list($insql, $params) = $this->get_db()->get_in_or_equal($discussionids, SQL_PARAMS_NAMED);
 501  
 502          $sql = "
 503              SELECT p.*
 504                FROM {" . self::TABLE . "} p
 505                JOIN (
 506                  SELECT mp.discussion, MIN(mp.created) AS created
 507                    FROM {" . self::TABLE . "} mp
 508                   WHERE mp.discussion {$insql}
 509                GROUP BY mp.discussion
 510                ) lp ON lp.discussion = p.discussion AND lp.created = p.created";
 511  
 512          $records = $this->get_db()->get_records_sql($sql, $params);
 513          return $this->transform_db_records_to_entities($records);
 514      }
 515  
 516      /**
 517       * Get the posts for the given user.
 518       *
 519       * @param int $discussionid The discussion to fetch posts for
 520       * @param int $userid The user to fetch posts for
 521       * @param bool $canseeprivatereplies Whether this user can see all private replies or not
 522       * @param string $orderby Order the results
 523       * @return post_entity[]
 524       */
 525      public function get_posts_in_discussion_for_user_id(
 526          int $discussionid,
 527          int $userid,
 528          bool $canseeprivatereplies,
 529          string $orderby = 'created ASC'
 530      ): array {
 531          $user = $this->get_db()->get_record('user', ['id' => (int)$userid], '*', IGNORE_MISSING);
 532  
 533          $alias = $this->get_table_alias();
 534          [
 535              'where' => $privatewhere,
 536              'params' => $privateparams,
 537          ] = $this->get_private_reply_sql($user, $canseeprivatereplies);
 538  
 539          $wheresql = "{$alias}.userid = :authorid AND
 540                       {$alias}.discussion = :discussionid {$privatewhere}";
 541          $orderbysql = $alias . '.' . $orderby;
 542  
 543          $sql = $this->generate_get_records_sql($wheresql, $orderbysql);
 544          $records = $this->get_db()->get_records_sql($sql, array_merge([
 545              'authorid' => $userid,
 546              'discussionid' => $discussionid
 547          ], $privateparams));
 548  
 549          return $this->transform_db_records_to_entities($records);
 550      }
 551  }