Counting TYPO3 pages in a branch

gistfile1.txt

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;