restructure the query in BlubberThread::findMyGlobalThreads() by using UNION...

Closes #5628

Merge request studip/studip!4245

2 files changed, 142 insertions, 33 deletions

diff --git a/lib/classes/SQLUnionQuery.php b/lib/classes/SQLUnionQuery.php
new file mode 100644
index 0000000..6254b78
--- /dev/null
+++ b/lib/classes/SQLUnionQuery.php
@@ -0,0 +1,87 @@
+<?php
+/**
+ * This class is to be used in combination with SQLQuery if you need to
+ * combine many SQLQuery objets into one single query using UNION.
+ */
+class SQLUnionQuery
+{
+    /**
+     * @var SQLQuery[]
+     */
+    protected array $queries;
+    protected bool $unionAll = false;
+
+    public function __construct(SQLQuery ...$queries)
+    {
+        $this->queries = $queries;
+    }
+
+    /**
+     * Adds a query to the union.
+     */
+    public function add(SQLQuery $query): void
+    {
+        $this->queries[] = $query;
+    }
+
+    /**
+     * Whether UNION ALL should be used or not
+     */
+    public function setUnionAll(bool $unionAll): void
+    {
+        $this->unionAll = $unionAll;
+    }
+
+    /**
+     * Returns the combined query
+     */
+    public function getQuery(): string
+    {
+        $queries    = [];
+        foreach ($this->queries as $query) {
+            $queries[] = $query->show();
+        }
+
+        $query = implode(
+            $this->unionAll ? ' UNION ALL ' : ' UNION ',
+            $queries
+        );
+
+        return $query;
+    }
+
+    /**
+     * Returns the used parameters
+     */
+    public function getParameters(): array
+    {
+        $parameters = [];
+        foreach ($this->queries as $query) {
+            $parameters = array_merge($parameters, $query->settings['parameter'] ?? []);
+        }
+
+        return $parameters;
+    }
+
+    /**
+     * Fetches all rows from the combined query
+     */
+    public function fetchAll(callable $callable = null): array
+    {
+        return DBManager::get()->fetchAll(
+            $this->getQuery(),
+            $this->getParameters(),
+            $callable
+        );
+    }
+
+    /**
+     * Fetches a single column from all rows of the combined query
+     */
+    public function fetchFirst(int $columnNumber = 0): array
+    {
+        return $this->fetchAll(function ($row) use ($columnNumber) {
+            return array_values($row)[$columnNumber];
+        });
+    }
+}
diff --git a/lib/models/BlubberThread.php b/lib/models/BlubberThread.php
index 49478f2..f57e88c 100644
--- a/lib/models/BlubberThread.php
+++ b/lib/models/BlubberThread.php
@@ -160,47 +160,69 @@ class BlubberThread extends SimpleORMap implements PrivacyObject
                         AND UNIX_TIMESTAMP() - blubber_threads.mkdate > 60 * 60";
         self::deleteBySQL($condition);
 
+        $union = new SQLUnionQuery();
+        $union->setUnionAll(true);
+
+        // Public, global and mentions
+        $query = new SQLQuery('blubber_threads');
+        $query->where('visible_in_stream = 1');
+        if ($GLOBALS['perm']->have_perm('root')) {
+            $query->where('context_type', "context_type = :context_type", [':context_type' => 'public']);
+        } else {
+            $query->where(
+                'context_type',
+                "context_type IN (:context_type)",
+                [':context_type' => ['public', 'course', 'institute']]
+            );
 
+        }
+        $query->where(
+            'public/global/mentions',
+            implode(' OR ', [
+                "blubber_threads.thread_id = 'global'",
+                "user_id = :user_id",
+                "thread_id IN (SELECT thread_id FROM blubber_comments WHERE user_id = :user_id)"
+            ]),
+            [':user_id' => $user_id]
+        );
+        $union->add($query);
+
+        // Courses
+        $course_ids = self::getMyBlubberCourses($user_id);
+        if (count($course_ids) > 0) {
+            $query = new SQLQuery('blubber_threads');
+            $query->where('visible_in_stream = 1');
+            $query->where('inst_type', "context_type = 'course'");
+            $query->where('inst_ids', 'context_id IN (:course_ids)', [':course_ids' => $course_ids]);
+            $union->add($query);
+        }
 
-        $query = SQLQuery::table('blubber_threads');
-        $query->join('my_comments', 'blubber_comments', 'blubber_threads.thread_id = my_comments.thread_id AND my_comments.user_id = :user_id', 'LEFT JOIN');
-        $query->join('blubber_mentions', 'blubber_mentions', 'blubber_mentions.thread_id = blubber_threads.thread_id', 'LEFT JOIN');
-
-        if (!$GLOBALS['perm']->have_perm('admin', $user_id)) {
-            //user, autor, tutor, dozent
-            $query->where('mycourses', implode(' OR ', [
-                "(blubber_threads.context_type = 'public' AND (my_comments.comment_id IS NOT NULL OR blubber_threads.user_id = :user_id OR blubber_threads.thread_id = 'global'))",
-                "(blubber_threads.context_type = 'course' AND blubber_threads.context_id IN (:seminar_ids))",
-                "(blubber_threads.context_type = 'institute' AND blubber_threads.context_id IN (:institut_ids))",
-                "(blubber_threads.context_type = 'private' AND blubber_mentions.user_id = :user_id AND blubber_mentions.external_contact = 0)",
-            ]), [
-                'seminar_ids'  => self::getMyBlubberCourses($user_id),
-                'institut_ids' => self::getMyBlubberInstitutes($user_id),
-            ]);
-        } elseif (!$GLOBALS['perm']->have_perm('root', $user_id)) {
-            //admin
-            $query->where('mycourses', implode(' OR ', [
-                "(blubber_threads.context_type = 'public' AND (my_comments.comment_id IS NOT NULL OR blubber_threads.user_id = :user_id OR blubber_threads.thread_id = 'global'))",
-                "(blubber_threads.context_type = 'institute' AND blubber_threads.context_id IN (:institut_ids))",
-                "(blubber_threads.context_type = 'private' AND blubber_mentions.user_id = :user_id AND blubber_mentions.external_contact = 0)",
-            ]), ['institut_ids' => self::getMyBlubberInstitutes($user_id)]);
-        } else {
-            //root
-            $query->where(implode(' OR ', [
-                "(blubber_threads.context_type IN ('public', 'course', 'institute') AND (my_comments.comment_id IS NOT NULL OR blubber_threads.user_id = :user_id OR blubber_threads.thread_id = 'global'))",
-                "(blubber_threads.context_type = 'private' AND blubber_mentions.user_id = :user_id AND blubber_mentions.external_contact = '0')",
-            ]));
+        // Institutes
+        $institute_ids = self::getMyBlubberInstitutes($user_id);
+        if (count($institute_ids) > 0) {
+            $query = new SQLQuery('blubber_threads');
+            $query->where('visible_in_stream = 1');
+            $query->where('inst_type', "context_type = 'institute'");
+            $query->where('inst_ids', 'context_id IN (:institute_ids)', [':institute_ids' => $institute_ids]);
+            $union->add($query);
         }
-        $query->where("blubber_threads.visible_in_stream = 1");
-        $query->parameter('user_id', $user_id);
-        $query->groupBy('blubber_threads.thread_id');
 
-        $thread_ids = $query->fetchAll("thread_id");
+        // Private mentions
+        $query = new SQLQuery('blubber_threads');
+        $query->join('blubber_mentions', 'blubber_mentions', 'blubber_mentions.thread_id = blubber_threads.thread_id', 'JOIN');
+        $query->where("context_type = 'private'");
+        $query->where('visible_in_stream = 1');
+        $query->where('user', 'blubber_mentions.user_id = :user_id', [':user_id' => $user_id]);
+        $query->where('blubber_mentions.external_contact = 0');
+
+        $union->add($query);
+
+        $thread_ids = $union->fetchFirst();
 
         $threads = [];
 
         do {
-            list($newthreads, $filtered, $new_since, $new_olderthan) = self::getOrderedThreads(
+            [$newthreads, $filtered, $new_since, $new_olderthan] = self::getOrderedThreads(
                 $thread_ids,
                 $limit - count($threads),
                 $since,