Delete orphaned FK rows via GC

brandonkelly · brandonkelly · commit 80d5c39c498e · 2024-12-04T16:03:34.000-08:00
diff --git a/CHANGELOG-WIP.md b/CHANGELOG-WIP.md
@@ -8,4 +8,5 @@
 - The Queue Manager utility now shows jobs’ class names. ([#16228](https://github.com/craftcms/cms/pull/16228)) 
 
 ## System
+- Database rows with foreign keys referencing nonexistent rows are now deleted via garbage collection.
 - Updated Twig to 3.15. ([#16207](https://github.com/craftcms/cms/discussions/16207))
diff --git a/src/services/Gc.php b/src/services/Gc.php
@@ -15,6 +15,7 @@
 use craft\db\Connection;
 use craft\db\Query;
 use craft\db\Table;
+use craft\db\TableSchema;
 use craft\elements\Asset;
 use craft\elements\Category;
 use craft\elements\Entry;
@@ -151,6 +152,7 @@ public function run(bool $force = false): void
         $this->_deleteOrphanedSearchIndexes();
         $this->_deleteOrphanedRelations();
         $this->_deleteOrphanedStructureElements();
+        $this->_deleteOrphanedFkRows();
 
         $this->_hardDeleteStructures();
 
@@ -525,6 +527,57 @@ private function _deleteOrphanedStructureElements(): void
         $this->_stdout("done\n", Console::FG_GREEN);
     }
 
+    private function _deleteOrphanedFkRows(): void
+    {
+        $this->_stdout('    > deleting orphaned foreign key rows ... ');
+
+        // Disable FK checks
+        $qb = $this->db->getSchema()->getQueryBuilder();
+        $this->db->createCommand($qb->checkIntegrity(false))->execute();
+
+        $isMysql = $this->db->getIsMysql();
+        foreach ($this->db->getSchema()->getTableSchemas() as $table) {
+            /** @var TableSchema $table */
+            $extendedFkInfo = $table->getExtendedForeignKeys();
+            $counter = 0;
+            foreach ($table->foreignKeys as $fk) {
+                if ($extendedFkInfo[$counter]['deleteType'] === 'CASCADE') {
+                    $fk = array_merge($fk);
+                    $refTable = array_shift($fk);
+
+                    foreach ($fk as $fkColumn => $pkColumn) {
+                        if ($isMysql) {
+                            $sql = <<<SQL
+DELETE t.* FROM $table->name t
+LEFT JOIN $refTable t2 ON t2.$pkColumn = t.$fkColumn
+WHERE t.$fkColumn IS NOT NULL
+AND t2.$pkColumn IS NULL
+SQL;
+                        } else {
+                            $sql = <<<SQL
+DELETE FROM $table->name t
+WHERE t2.$pkColumn IS NULL
+AND NOT EXISTS (
+    SELECT * FROM $refTable
+    WHERE "$pkColumn" = t."$fkColumn"
+)
+SQL;
+                        }
+
+                        $this->db->createCommand($sql)->execute();
+                    }
+                }
+
+                $counter++;
+            }
+        }
+
+        // Re-enable FK checks
+        $this->db->createCommand($qb->checkIntegrity())->execute();
+
+        $this->_stdout("done\n", Console::FG_GREEN);
+    }
+
     /**
      * Deletes field layouts that are no longer used.
      *
