// table and join to the userid there. If we are looking for posts then we need
// to join to the forum_posts table.
if (!$discussionsonly) {
- $joinsql = 'JOIN {forum_discussions} fd ON fd.course = c.id
- JOIN {forum_posts} fp ON fp.discussion = fd.id';
- $wheresql = 'fp.userid = :userid';
- $params = array('userid' => $user->id);
+ $subquery = "(SELECT DISTINCT fd.course
+ FROM {forum_discussions} fd
+ JOIN {forum_posts} fp ON fp.discussion = fd.id
+ WHERE fp.userid = :userid )";
} else {
- $joinsql = 'JOIN {forum_discussions} fd ON fd.course = c.id';
- $wheresql = 'fd.userid = :userid';
- $params = array('userid' => $user->id);
+ $subquery= "(SELECT DISTINCT fd.course
+ FROM {forum_discussions} fd
+ WHERE fd.userid = :userid )";
}
+ $params = array('userid' => $user->id);
+
// Join to the context table so that we can preload contexts if required.
if ($includecontexts) {
$ctxselect = ', ' . context_helper::get_preload_record_columns_sql('ctx');
// Now we need to get all of the courses to search.
// All courses where the user has posted within a forum will be returned.
- $sql = "SELECT DISTINCT c.* $ctxselect
+ $sql = "SELECT c.* $ctxselect
FROM {course} c
- $joinsql
$ctxjoin
- WHERE $wheresql";
+ WHERE c.id IN ($subquery)";
$courses = $DB->get_records_sql($sql, $params, $limitfrom, $limitnum);
if ($includecontexts) {
array_map('context_helper::preload_from_record', $courses);