cleanup blubber and delete correctly, fixes #4097

Closes #4097

Merge request studip/studip!2943

3 files changed, 63 insertions, 12 deletions

diff --git a/db/migrations/5.1.56_cleanup_blubber.php b/db/migrations/5.1.56_cleanup_blubber.php
new file mode 100644
index 0000000..86348bc
--- /dev/null
+++ b/db/migrations/5.1.56_cleanup_blubber.php
@@ -0,0 +1,46 @@
+<?php
+/**
+ * @see https://gitlab.studip.de/studip/studip/-/issues/4097
+ */
+return new class extends Migration
+{
+    public function description()
+    {
+        return 'Removes orhpaned blubber entries';
+    }
+
+    protected function up()
+    {
+        $query = "DELETE bt, ouv
+                  FROM `blubber_threads` AS bt
+                  LEFT JOIN `object_user_visits` AS ouv
+                    ON (bt.`thread_id` = ouv.`object_id`)
+                  WHERE bt.`external_contact` = 0
+                    AND bt.`user_id` NOT IN (
+                        SELECT `user_id` FROM `auth_user_md5`
+                    )";
+        DBManager::get()->exec($query);
+
+        $query = "DELETE FROM `blubber_comments`
+                  WHERE (
+                      `external_contact` = 0
+                      AND `user_id` NOT IN (
+                          SELECT `user_id` FROM `auth_user_md5`
+                      )
+                    ) OR `thread_id` NOT IN (
+                      SELECT `thread_id` FROM `blubber_threads`
+                    )";
+        DBManager::get()->exec($query);
+
+        $query = "DELETE FROM `blubber_mentions`
+                  WHERE (
+                      `external_contact` = 0
+                      AND `user_id` NOT IN (
+                          SELECT `user_id` FROM `auth_user_md5`
+                      )
+                    ) OR `thread_id` NOT IN (
+                    SELECT `thread_id` FROM `blubber_threads`
+                  )";
+        DBManager::get()->exec($query);
+    }
+};
diff --git a/lib/classes/UserManagement.class.php b/lib/classes/UserManagement.class.php
index 3851e5e..c670b76 100644
--- a/lib/classes/UserManagement.class.php
+++ b/lib/classes/UserManagement.class.php
@@ -1113,18 +1113,6 @@ class UserManagement
         // delete the datafields
         $localEntries = DataFieldEntry::removeAll($user_id);
 
-        // delete all blubber entrys
-        $query = "DELETE blubber_threads, blubber_mentions, blubber_comments
-                  FROM blubber_threads
-                  LEFT JOIN blubber_mentions USING (user_id)
-                  LEFT JOIN blubber_comments USING (user_id)
-                  WHERE user_id = ?";
-        $statement = DBManager::get()->prepare($query);
-        $statement->execute([$user_id]);
-        if ($count = $statement->rowCount()) {
-            $msg .= 'info§' . sprintf(_('%s Blubber gelöscht.'), $count) . '§';
-        }
-
         // delete user from waiting lists
         $query = "SELECT seminar_id FROM admission_seminar_user WHERE user_id = ?";
         $statement = DBManager::get()->prepare($query);
diff --git a/lib/models/User.class.php b/lib/models/User.class.php
index 146723e..de86382 100644
--- a/lib/models/User.class.php
+++ b/lib/models/User.class.php
@@ -65,6 +65,9 @@
  * @property SimpleORMapCollection datafields has_many DatafieldEntryModel
  * @property SimpleORMapCollection studycourses has_many UserStudyCourse
  * @property SimpleORMapCollection contacts has_many Contact
+ * @property SimpleORMapCollection|BlubberThread[] $blubber_threads has_many BlubberThread
+ * @property SimpleORMapCollection|BlubberComment[] $blubber_comments has_many BlubberComment
+ * @property SimpleORMapCollection|BlubberMention[] $blubber_mentions has_many BlubberMention
  * @property UserInfo   info   has_one UserInfo
  * @property UserOnline online has_one UserOnline
  * @property Kategorie[]|SimpleORMapCollection $profile_categories has_many Kategorie
@@ -191,6 +194,20 @@ class User extends AuthUserMd5 implements Range, PrivacyObject
             'on_delete'         => 'delete',
         ];
 
+        // Blubber relations
+        $config['has_many']['blubber_threads'] = [
+            'class_name' => BlubberThread::class,
+            'on_delete'  => 'delete',
+        ];
+        $config['has_many']['blubber_comments'] = [
+            'class_name' => BlubberComment::class,
+            'on_delete'  => 'delete',
+        ];
+        $config['has_many']['blubber_mentions'] = [
+            'class_name' => BlubberMention::class,
+            'on_delete'  => 'delete',
+        ];
+
         $config['additional_fields']['config']['get'] = function ($user) {
             return UserConfig::get($user->id);
         };