Skip to content

Instantly share code, notes, and snippets.

@kraftb
Created February 6, 2014 13:33
Show Gist options
  • Save kraftb/8844198 to your computer and use it in GitHub Desktop.
Save kraftb/8844198 to your computer and use it in GitHub Desktop.
Counting TYPO3 pages in a branch
1. Create a table which helps us for this task:
CREATE TABLE ClosureTable (ancestor_id INT NOT NULL, descendant_id INT NOT NULL, path_length INT NOT NULL, PRIMARY KEY (ancestor_id, descendant_id));
2. Execute the following queries:
INSERT INTO ClosureTable (ancestor_id, descendant_id, path_length) SELECT 0,0,0;
INSERT INTO ClosureTable (ancestor_id, descendant_id, path_length) SELECT uid, uid, 0 FROM pages;
INSERT INTO ClosureTable (ancestor_id, descendant_id, path_length) SELECT pid, uid, 1 FROM pages;
SET @level=1;
3. Execute the following queries in the shown order as long until the first query results in "0 rows affected"
INSERT INTO ClosureTable (ancestor_id, descendant_id, path_length) SELECT ct.ancestor_id, p1.uid, @level+1 FROM pages AS p1, ClosureTable AS ct WHERE p1.pid=ct.descendant_id AND ct.path_length=@level;
SET @level=@level+1;
4. Use the following query to select the number of pages below a specific PAGE_UID:
SELECT count(*) FROM ClosureTable WHERE ancestor_id=PAGE_UID;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment