MDL-65033 mod_forum: Modified sort sql for cross db support
[moodle.git] / mod / forum / classes / local / vaults / discussion_list.php
CommitLineData
47d38303
RW
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
25namespace mod_forum\local\vaults;
26
27defined('MOODLE_INTERNAL') || die();
28
29use mod_forum\local\vaults\preprocessors\extract_record as extract_record_preprocessor;
30use mod_forum\local\vaults\preprocessors\extract_user as extract_user_preprocessor;
31use mod_forum\local\renderers\discussion_list as discussion_list_renderer;
71cbc055 32use core\dml\table as dml_table;
47d38303
RW
33use stdClass;
34
35/**
36 * Discussion list vault.
37 *
38 * This should be the only place that accessed the database.
39 *
40 * This uses the repository pattern. See:
41 * https://designpatternsphp.readthedocs.io/en/latest/More/Repository/README.html
42 *
43 * @package mod_forum
44 * @copyright 2019 Andrew Nicols <andrew@nicols.co.uk>
45 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
46 */
47class discussion_list extends db_table_vault {
48 /** The table for this vault */
49 private const TABLE = 'forum_discussions';
50 /** Alias for first author id */
9e5cdcd5 51 private const FIRST_AUTHOR_ID_ALIAS = 'fauserpictureid';
47d38303 52 /** Alias for author fields */
9e5cdcd5 53 private const FIRST_AUTHOR_ALIAS = 'fauserrecord';
47d38303 54 /** Alias for last author id */
9e5cdcd5 55 private const LATEST_AUTHOR_ID_ALIAS = 'lauserpictureid';
47d38303 56 /** Alias for last author fields */
9e5cdcd5 57 private const LATEST_AUTHOR_ALIAS = 'lauserrecord';
47d38303
RW
58 /** Default limit */
59 public const PAGESIZE_DEFAULT = 100;
60
61 /** Sort by newest first */
62 public const SORTORDER_NEWEST_FIRST = 1;
63 /** Sort by oldest first */
64 public const SORTORDER_OLDEST_FIRST = 2;
94552394
RW
65 /** Sort by created desc */
66 public const SORTORDER_CREATED_DESC = 3;
47d38303
RW
67
68 /**
69 * Get the table alias.
70 *
71 * @return string
72 */
73 protected function get_table_alias() : string {
74 return 'd';
75 }
76
309e8698
P
77 /**
78 * Get the favourite table alias
79 *
80 * @return string
81 */
99bda8a7
P
82 protected function get_favourite_alias() : string {
83 return 'favalias';
84 }
85
47d38303
RW
86 /**
87 * Build the SQL to be used in get_records_sql.
88 *
89 * @param string|null $wheresql Where conditions for the SQL
90 * @param string|null $sortsql Order by conditions for the SQL
8885cd57
P
91 * @param string|null $joinsql Additional join conditions for the sql
92 * @param stdClass|null $user User we are performing this query for
309e8698 93 *
47d38303
RW
94 * @return string
95 */
8885cd57 96 protected function generate_get_records_sql(string $wheresql = null, ?string $sortsql = null, stdClass $user = null) : string {
47d38303
RW
97 $alias = $this->get_table_alias();
98 $db = $this->get_db();
99
d3cac88d
P
100 list($favsql, $favparams) = $this->get_favourite_sql($user);
101 foreach ($favparams as $key => $param) {
102 $favsql = str_replace(":$key", "'$param'", $favsql);
8885cd57
P
103 }
104
47d38303
RW
105 // Fetch:
106 // - Discussion
107 // - First post
108 // - Author
109 // - Most recent editor.
71cbc055
AN
110 $thistable = new dml_table(self::TABLE, $alias, $alias);
111 $posttable = new dml_table('forum_posts', 'fp', 'p_');
47d38303
RW
112 $firstauthorfields = \user_picture::fields('fa', null, self::FIRST_AUTHOR_ID_ALIAS, self::FIRST_AUTHOR_ALIAS);
113 $latestuserfields = \user_picture::fields('la', null, self::LATEST_AUTHOR_ID_ALIAS, self::LATEST_AUTHOR_ALIAS);
114
115 $fields = implode(', ', [
71cbc055
AN
116 $thistable->get_field_select(),
117 $posttable->get_field_select(),
47d38303
RW
118 $firstauthorfields,
119 $latestuserfields,
120 ]);
121
71cbc055 122 $tables = $thistable->get_from_sql();
47d38303
RW
123 $tables .= ' JOIN {user} fa ON fa.id = ' . $alias . '.userid';
124 $tables .= ' JOIN {user} la ON la.id = ' . $alias . '.usermodified';
71cbc055 125 $tables .= ' JOIN ' . $posttable->get_from_sql() . ' ON fp.id = ' . $alias . '.firstpost';
ac915316 126 $tables .= isset($favsql) ? $favsql : '';
47d38303
RW
127
128 $selectsql = 'SELECT ' . $fields . ' FROM ' . $tables;
129 $selectsql .= $wheresql ? ' WHERE ' . $wheresql : '';
130 $selectsql .= $sortsql ? ' ORDER BY ' . $sortsql : '';
131
132 return $selectsql;
133 }
134
135 /**
136 * Build the SQL to be used in count_records_sql.
137 *
138 * @param string|null $wheresql Where conditions for the SQL
139 * @return string
140 */
141 protected function generate_count_records_sql(string $wheresql = null) : string {
142 $alias = $this->get_table_alias();
143 $db = $this->get_db();
144
145 $selectsql = "SELECT COUNT(1) FROM {" . self::TABLE . "} {$alias}";
146 $selectsql .= $wheresql ? ' WHERE ' . $wheresql : '';
147
148 return $selectsql;
149 }
150
151 /**
152 * Get a list of preprocessors to execute on the DB results before being converted
153 * into entities.
154 *
155 * @return array
156 */
157 protected function get_preprocessors() : array {
158 return array_merge(
159 parent::get_preprocessors(),
160 [
71cbc055
AN
161 'discussion' => new extract_record_preprocessor(self::TABLE, $this->get_table_alias()),
162 'firstpost' => new extract_record_preprocessor('forum_posts', 'p_'),
47d38303
RW
163 'firstpostauthor' => new extract_user_preprocessor(self::FIRST_AUTHOR_ID_ALIAS, self::FIRST_AUTHOR_ALIAS),
164 'latestpostauthor' => new extract_user_preprocessor(self::LATEST_AUTHOR_ID_ALIAS, self::LATEST_AUTHOR_ALIAS),
165 ]
166 );
167 }
168
169 /**
170 * Convert the DB records into discussion list entities.
171 *
172 * @param array $results The DB records
173 * @return discussion_list[]
174 */
175 protected function from_db_records(array $results) {
176 $entityfactory = $this->get_entity_factory();
177
178 return array_map(function(array $result) use ($entityfactory) {
179 [
180 'discussion' => $discussion,
181 'firstpost' => $firstpost,
182 'firstpostauthor' => $firstpostauthor,
183 'latestpostauthor' => $latestpostauthor,
184 ] = $result;
185 return $entityfactory->get_discussion_summary_from_stdclass(
186 $discussion,
187 $firstpost,
188 $firstpostauthor,
189 $latestpostauthor
190 );
191 }, $results);
192 }
193
194 /**
195 * Get the sort order SQL for a sort method.
196 *
197 * @param int|null $sortmethod
198 */
d3cac88d 199 public function get_sort_order(?int $sortmethod, $includefavourites = true) : string {
47d38303
RW
200 global $CFG;
201
202 $alias = $this->get_table_alias();
203
309e8698
P
204 if ($sortmethod == self::SORTORDER_CREATED_DESC) {
205 $keyfield = "fp.created";
206 $direction = "DESC";
207 } else {
208 $keyfield = "{$alias}.timemodified";
209 $direction = "DESC";
94552394 210
309e8698
P
211 if ($sortmethod == self::SORTORDER_OLDEST_FIRST) {
212 $direction = "ASC";
213 }
94552394 214
309e8698
P
215 if (!empty($CFG->forum_enabletimedposts)) {
216 $keyfield = "CASE WHEN {$keyfield} < {$alias}.timestart THEN {$alias}.timestart ELSE {$keyfield} END";
217 }
47d38303
RW
218 }
219
30513f97
RW
220 $favouritesort = '';
221 if ($includefavourites) {
222 $favalias = $this->get_favourite_alias();
223 // Since we're joining on the favourite table any discussion that isn't favourited will have
224 // null in the favourite columns. Nulls behave differently in the sorting for different databases.
225 // We can ensure consistency between databases by explicitly deprioritising any null favourite field
226 // using a case statement.
227 $favouritesort = ", CASE WHEN {$favalias}.id IS NULL THEN 0 ELSE 1 END DESC";
228 // After the null favourite fields are deprioritised and appear below the favourited discussions we
229 // need to order the favourited discussions by id so that the most recently favourited discussions
230 // appear at the top of the list.
231 $favouritesort .= ", {$favalias}.id DESC";
232 }
d3cac88d
P
233
234 return "{$alias}.pinned DESC $favouritesort , {$keyfield} {$direction}";
47d38303
RW
235 }
236
237 /**
238 * Fetch any required SQL to respect timed posts.
239 *
240 * @param bool $includehiddendiscussions Whether to include hidden discussions or not
241 * @param int $includepostsforuser Which user to include posts for, if any
242 * @return array The SQL and parameters to include
243 */
244 protected function get_hidden_post_sql(bool $includehiddendiscussions, ?int $includepostsforuser) {
245 $wheresql = '';
246 $params = [];
247 if (!$includehiddendiscussions) {
248 $now = time();
249 $wheresql = " AND ((d.timestart <= :timestart AND (d.timeend = 0 OR d.timeend > :timeend))";
250 $params['timestart'] = $now;
251 $params['timeend'] = $now;
252 if (null !== $includepostsforuser) {
253 $wheresql .= " OR d.userid = :byuser";
254 $params['byuser'] = $includepostsforuser;
255 }
256 $wheresql .= ")";
257 }
258
259 return [
260 'wheresql' => $wheresql,
261 'params' => $params,
262 ];
263 }
264
265 /**
266 * Get each discussion, first post, first and last post author for the given forum, considering timed posts, and
267 * pagination.
268 *
269 * @param int $forumid The forum to fetch the discussion set for
270 * @param bool $includehiddendiscussions Whether to include hidden discussions or not
271 * @param int|null $includepostsforuser Which user to include posts for, if any
272 * @param int $sortorder The sort order to use
273 * @param int $limit The number of discussions to fetch
274 * @param int $offset The record offset
275 * @return array The set of data fetched
276 */
277 public function get_from_forum_id(
278 int $forumid,
279 bool $includehiddendiscussions,
280 ?int $includepostsforuser,
281 ?int $sortorder,
282 int $limit,
8885cd57
P
283 int $offset,
284 stdClass $user
47d38303
RW
285 ) {
286 $alias = $this->get_table_alias();
287 $wheresql = "{$alias}.forum = :forumid";
288 [
289 'wheresql' => $hiddensql,
290 'params' => $hiddenparams
291 ] = $this->get_hidden_post_sql($includehiddendiscussions, $includepostsforuser);
292 $wheresql .= $hiddensql;
293
294 $params = array_merge($hiddenparams, [
295 'forumid' => $forumid,
296 ]);
297
d3cac88d 298 $sql = $this->generate_get_records_sql($wheresql, $this->get_sort_order($sortorder, isloggedin()), $user);
47d38303
RW
299 $records = $this->get_db()->get_records_sql($sql, $params, $offset, $limit);
300
301 return $this->transform_db_records_to_entities($records);
302 }
303
304 /**
305 * Get each discussion, first post, first and last post author for the given forum, and the set of groups to display
306 * considering timed posts, and pagination.
307 *
308 * @param int $forumid The forum to fetch the discussion set for
309 * @param int[] $groupids The list of real groups to filter on
310 * @param bool $includehiddendiscussions Whether to include hidden discussions or not
311 * @param int|null $includepostsforuser Which user to include posts for, if any
312 * @param int $sortorder The sort order to use
313 * @param int $limit The number of discussions to fetch
314 * @param int $offset The record offset
315 * @return array The set of data fetched
316 */
317 public function get_from_forum_id_and_group_id(
318 int $forumid,
319 array $groupids,
320 bool $includehiddendiscussions,
321 ?int $includepostsforuser,
322 ?int $sortorder,
323 int $limit,
8885cd57
P
324 int $offset,
325 stdClass $user
47d38303
RW
326 ) {
327 $alias = $this->get_table_alias();
328
329 $wheresql = "{$alias}.forum = :forumid AND ";
330 $groupparams = [];
331 if (empty($groupids)) {
332 $wheresql .= "{$alias}.groupid = :allgroupsid";
333 } else {
334 list($insql, $groupparams) = $this->get_db()->get_in_or_equal($groupids, SQL_PARAMS_NAMED, 'gid');
335 $wheresql .= "({$alias}.groupid = :allgroupsid OR {$alias}.groupid {$insql})";
336 }
337
338 [
339 'wheresql' => $hiddensql,
340 'params' => $hiddenparams
341 ] = $this->get_hidden_post_sql($includehiddendiscussions, $includepostsforuser);
342 $wheresql .= $hiddensql;
343
344 $params = array_merge($hiddenparams, $groupparams, [
345 'forumid' => $forumid,
346 'allgroupsid' => -1,
347 ]);
348
d3cac88d 349 $sql = $this->generate_get_records_sql($wheresql, $this->get_sort_order($sortorder, isloggedin()), $user);
47d38303
RW
350 $records = $this->get_db()->get_records_sql($sql, $params, $offset, $limit);
351
352 return $this->transform_db_records_to_entities($records);
353 }
354
355 /**
356 * Count the number of discussions in the forum.
357 *
358 * @param int $forumid Id of the forum to count discussions in
359 * @param bool $includehiddendiscussions Include hidden dicussions in the count?
360 * @param int|null $includepostsforuser Include discussions created by this user in the count
361 * (only works if not including hidden discussions).
362 * @return int
363 */
364 public function get_total_discussion_count_from_forum_id(
365 int $forumid,
366 bool $includehiddendiscussions,
367 ?int $includepostsforuser
368 ) {
369 $alias = $this->get_table_alias();
370
371 $wheresql = "{$alias}.forum = :forumid";
372
373 [
374 'wheresql' => $hiddensql,
375 'params' => $hiddenparams
376 ] = $this->get_hidden_post_sql($includehiddendiscussions, $includepostsforuser);
377 $wheresql .= $hiddensql;
378
379 $params = array_merge($hiddenparams, [
380 'forumid' => $forumid,
381 ]);
382
383 return $this->get_db()->count_records_sql($this->generate_count_records_sql($wheresql), $params);
384 }
385
386 /**
387 * Count the number of discussions in all groups and the list of groups provided.
388 *
389 * @param int $forumid Id of the forum to count discussions in
390 * @param int[] $groupids List of group ids to include in the count (discussions in all groups will always be counted)
391 * @param bool $includehiddendiscussions Include hidden dicussions in the count?
392 * @param int|null $includepostsforuser Include discussions created by this user in the count
393 * (only works if not including hidden discussions).
394 * @return int
395 */
396 public function get_total_discussion_count_from_forum_id_and_group_id(
397 int $forumid,
398 array $groupids,
399 bool $includehiddendiscussions,
400 ?int $includepostsforuser
401 ) {
402 $alias = $this->get_table_alias();
403
404 $wheresql = "{$alias}.forum = :forumid AND ";
405 $groupparams = [];
406 if (empty($groupids)) {
407 $wheresql .= "{$alias}.groupid = :allgroupsid";
408 } else {
409 list($insql, $groupparams) = $this->get_db()->get_in_or_equal($groupids, SQL_PARAMS_NAMED, 'gid');
410 $wheresql .= "({$alias}.groupid = :allgroupsid OR {$alias}.groupid {$insql})";
411 }
412
413 [
414 'wheresql' => $hiddensql,
415 'params' => $hiddenparams
416 ] = $this->get_hidden_post_sql($includehiddendiscussions, $includepostsforuser);
417 $wheresql .= $hiddensql;
418
419 $params = array_merge($hiddenparams, $groupparams, [
420 'forumid' => $forumid,
421 'allgroupsid' => -1,
422 ]);
423
424 return $this->get_db()->count_records_sql($this->generate_count_records_sql($wheresql), $params);
425 }
99bda8a7
P
426
427 /**
428 * Get the standard favouriting sql.
13cd05ac
P
429 *
430 * @param stdClass $user The user we are getting the sql for
431 * @return [$sql, $params] An array comprising of the sql and any associated params
99bda8a7 432 */
d3cac88d
P
433 private function get_favourite_sql(?stdClass $user): array {
434 $favsql = "";
435 $favparams = [];
436
437 if ($user && isloggedin()) {
438 $usercontext = \context_user::instance($user->id);
439 $alias = $this->get_table_alias();
440 $ufservice = \core_favourites\service_factory::get_service_for_user_context($usercontext);
441 list($favsql, $favparams) = $ufservice->get_join_sql_by_type('mod_forum', 'discussions',
442 $this->get_favourite_alias(), "$alias.id");
443 }
99bda8a7
P
444
445 return [$favsql, $favparams];
446 }
47d38303 447}