Page Tree mit Closure Table
Verfasst: Mi 6. Jun 2018, 18:08
Es gibt verschiedene Möglichkeiten, eine "Baumstruktur" in der DB zu speichern. Ich gehe jetzt mal nicht auf deren Vor- und Nachteile ein, sondern auf eine Möglichkeit, die bisherige Struktur zu verbessern. Diese sieht in gekürzter Form etwa so aus:
Für jede Seite ist hinterlegt, welche Seite ihr übergeordnet ist. Faktisch haben wir in BC1 dann noch eine Spalte, die den "Pfad" der Seite beinhaltet, also die IDs aller Seiten, durch die man sich durchhangeln muss, um zu einer bestimmten Seite zu gelangen.
Closure Tables sind ein Hilfsmittel, um diverse Abfragen zu ermöglichen, die sonst relativ mühselig sind. (Bitte Suchmaschine der Wahl benutzen, um Details zu erfahren.) Hier speichert man zu jeder Seite, welche anderen Seiten ihr untergeordnet sind, und zwar egal in welcher Tiefe. Für BC könnte das so aussehen:
Zum Befüllen einer solchen Closure Table kann man eine Stored Procedure nehmen.
Nun sollte es z.B. möglich sein, die Anzahl der nachgeordneten Seiten (descendants) zu einer Seite zu ermitteln. Per Einzelabfrage krieg ich das hin, aber nicht für alle Seiten auf einen Rutsch. Entweder habe ich falsche Ergebnisse, oder es fehlt mir die Root-Ebene.
Vielleicht hat ja jemand Lust, die Nuß zu knacken.
https://stackoverflow.com/questions/282 ... re-pattern
Code: Alles auswählen
CREATE TABLE `pages` (
`page_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` TEXT NOT NULL COLLATE 'utf8mb4_bin',
`parent` INT(11) UNSIGNED NOT NULL
PRIMARY KEY (`page_id`)
);
Closure Tables sind ein Hilfsmittel, um diverse Abfragen zu ermöglichen, die sonst relativ mühselig sind. (Bitte Suchmaschine der Wahl benutzen, um Details zu erfahren.) Hier speichert man zu jeder Seite, welche anderen Seiten ihr untergeordnet sind, und zwar egal in welcher Tiefe. Für BC könnte das so aussehen:
Code: Alles auswählen
CREATE TABLE `pages_closure` (
`ancestor` INT(11) UNSIGNED NOT NULL,
`descendant` INT(11) UNSIGNED NOT NULL,
`depth` INT(11) UNSIGNED NOT NULL,
PRIMARY KEY (`ancestor`, `descendant`),
UNIQUE INDEX `ancestor_descendant` (`ancestor`, `descendant`),
INDEX `FK_pages_closure_pages_2` (`descendant`),
CONSTRAINT `FK_pages_closure_pages` FOREIGN KEY (`ancestor`) REFERENCES `pages` (`page_id`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `FK_pages_closure_pages_2` FOREIGN KEY (`descendant`) REFERENCES `pages` (`page_id`) ON UPDATE CASCADE ON DELETE CASCADE
);
Code: Alles auswählen
CREATE PROCEDURE `populate_pages_closure`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE distance int;
TRUNCATE TABLE pages_closure;
SET distance = 0;
-- seed closure with self-pairs (distance 0)
INSERT INTO pages_closure (ancestor, descendant, depth)
SELECT page_id, page_id, distance
FROM pages;
-- for each pair (root, leaf) in the closure,
-- add (root, leaf->child) from the base table
REPEAT
SET distance = distance + 1;
INSERT INTO pages_closure (ancestor, descendant, depth)
SELECT pages_closure.ancestor, pages.page_id, distance
FROM pages_closure, pages
WHERE pages_closure.descendant = pages.parent
AND pages_closure.depth = distance - 1;
UNTIL ROW_COUNT() = 0
END REPEAT;
END
Vielleicht hat ja jemand Lust, die Nuß zu knacken.
https://stackoverflow.com/questions/282 ... re-pattern