Page Tree mit Closure Table

Antworten
Benutzeravatar
shadowcat
Administrator
Beiträge: 5283
Registriert: Di 5. Feb 2013, 10:36
Kontaktdaten:

Page Tree mit Closure Table

Beitrag von shadowcat »

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:

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`)
);
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:

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
);
Zum Befüllen einer solchen Closure Table kann man eine Stored Procedure nehmen.

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
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
My software never has bugs, it just develops random features.
If it’s not broken, keep fixing it until it is
Benutzeravatar
shadowcat
Administrator
Beiträge: 5283
Registriert: Di 5. Feb 2013, 10:36
Kontaktdaten:

Re: Page Tree mit Closure Table

Beitrag von shadowcat »

Lösung.

Vollständige pages-Tabelle, gekürzt um die Spalten "root_id", "level" und "page_trail" (Name hier "pages_copy" da ich die Original-Tabelle zum Testen geklont habe):

Code: Alles auswählen

CREATE TABLE `cat_pages_copy` (
	`page_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`site_id` INT(11) UNSIGNED NOT NULL,
	`vis_id` INT(2) UNSIGNED NOT NULL DEFAULT '7',
	`parent` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	`link` TEXT NOT NULL,
	`page_title` VARCHAR(255) NOT NULL DEFAULT '',
	`menu_title` VARCHAR(255) NOT NULL DEFAULT '',
	`description` TEXT NULL DEFAULT NULL,
	`template` VARCHAR(255) NOT NULL DEFAULT '',
	`position` INT(11) NOT NULL DEFAULT '1',
	`menu` INT(11) NOT NULL DEFAULT '1',
	`language` VARCHAR(5) NOT NULL DEFAULT '',
	`searching` INT(11) NOT NULL DEFAULT '1',
	`created_by` INT(11) UNSIGNED NOT NULL DEFAULT '1',
	`modified_by` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	`modified_when` INT(11) NOT NULL DEFAULT '0',
	PRIMARY KEY (`page_id`),
	INDEX `FK_cat_pages_cat_visibility` (`vis_id`),
	INDEX `FK_cat_pages_cat_rbac_users` (`created_by`),
	INDEX `FK_cat_pages_cat_rbac_users_2` (`modified_by`),
	INDEX `FK_cat_pages_cat_sites` (`site_id`),
	CONSTRAINT `cat_pages_copy_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `cat_rbac_users` (`user_id`),
	CONSTRAINT `cat_pages_copy_ibfk_2` FOREIGN KEY (`modified_by`) REFERENCES `cat_rbac_users` (`user_id`),
	CONSTRAINT `cat_pages_copy_ibfk_3` FOREIGN KEY (`site_id`) REFERENCES `cat_sites` (`site_id`) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT `cat_pages_copy_ibfk_4` FOREIGN KEY (`vis_id`) REFERENCES `cat_visibility` (`vis_id`) ON UPDATE NO ACTION ON DELETE NO ACTION
);
Prozedur:

Code: Alles auswählen

CREATE DEFINER=`root`@`localhost` PROCEDURE `cat_populate_pages_closure`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
  DECLARE distance int;
  TRUNCATE TABLE cat_pages_closure;
  SET distance = 0;
  
  -- seed closure with self-pairs (distance 0)
  INSERT INTO cat_pages_closure (ancestor, descendant, depth)
    SELECT page_id, page_id, distance
      FROM cat_pages_copy;
      
  -- for each pair (root, leaf) in the closure,
  -- add (root, leaf->child) from the base table
  REPEAT
    SET distance = distance + 1;
    INSERT INTO cat_pages_closure (ancestor, descendant, depth)
      SELECT cat_pages_closure.ancestor, cat_pages_copy.page_id, distance
        FROM cat_pages_closure, cat_pages_copy
          WHERE cat_pages_closure.descendant = cat_pages_copy.parent
          AND cat_pages_closure.depth = distance - 1;
    UNTIL ROW_COUNT() = 0 
  END REPEAT;
END
Alle Seiten (kompletter Baum, Full-Menü):

Code: Alles auswählen

SELECT u.*
FROM cat_pages_closure AS c
    INNER JOIN `cat_pages_copy` AS u ON (u.page_id = c.descendant)
WHERE c.ancestor = 1 AND c.depth >= 1
Alle Seiten einer bestimmten Ebene (Siblings-Menü):

Code: Alles auswählen

SELECT u.*
FROM cat_pages_closure AS c
    INNER JOIN `cat_pages_copy` AS u ON (u.page_id = c.descendant)
WHERE c.ancestor = 1 AND c.depth = 1
Alle "Kindseiten" einer bestimmten Seiten (Submenü):

Code: Alles auswählen

SELECT u.*
FROM cat_pages_closure AS c
    INNER JOIN `cat_pages_copy` AS u ON (u.page_id = c.descendant)
WHERE c.ancestor = 53 AND c.depth >= 1
Voraussetzung: Eine unsichtbare "Mutter aller Seiten" auf Ebene 0 (depth 0).
Dateianhänge
2018-08-06 17_04_26-portable_blackcat_cat_pages_copy_ - HeidiSQL Portable 9.4.0.5125.png
2018-08-06 17_04_26-portable_blackcat_cat_pages_copy_ - HeidiSQL Portable 9.4.0.5125.png (6.19 KiB) 2405 mal betrachtet
My software never has bugs, it just develops random features.
If it’s not broken, keep fixing it until it is
Benutzeravatar
shadowcat
Administrator
Beiträge: 5283
Registriert: Di 5. Feb 2013, 10:36
Kontaktdaten:

Re: Page Tree mit Closure Table

Beitrag von shadowcat »

Achja, nachgeordnete Seiten zählen:

Code: Alles auswählen

SELECT count(*)
FROM cat_pages_closure AS c
    INNER JOIN `cat_pages_copy` AS u ON (u.page_id = c.descendant)
WHERE c.ancestor = 53 AND c.depth >= 1
My software never has bugs, it just develops random features.
If it’s not broken, keep fixing it until it is
Antworten