Ответ

G_Z 02.02.2016 18:43 / 02.02.2016 18:48

Схема и данные:

CREATE TABLE `_tree` (
	`page_id` INT(10) UNSIGNED NOT NULL,
	`ancestor_id` INT(10) UNSIGNED NOT NULL,
	`distance` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
	PRIMARY KEY (`page_id`, `ancestor_id`, `distance`),
	INDEX `idx_ancestor_id` (`ancestor_id`),
	CONSTRAINT `fk_ancestor_id` FOREIGN KEY (`ancestor_id`) REFERENCES `_tree` (`page_id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

INSERT INTO `_tree` (`page_id`, `ancestor_id`, `distance`)
VALUES
(0, 0, 0),
(1, 0, 1),
(1, 1, 0),
(2, 0, 2),
(2, 1, 1),
(2, 2, 0),
(3, 0, 2),
(3, 1, 1),
(3, 3, 0);

Вывод:

@main[]
$root(1)

^connect[$SQL.connect-string]{
	$tree[^table::sql{
		SELECT t.page_id, p.ancestor_id AS parent_id
		FROM _tree AS t
		JOIN _tree AS p ON (p.page_id = t.page_id AND p.distance = 1)
		WHERE t.ancestor_id = $root
	}]
}

$tree[^tree.hash[parent_id][
	$.type[table]
	$.distinct(true)
]]

<style>
	#tree div { border: 1px solid gray }
	#tree div div { margin-left: 50px }
</style>

<div id="tree">
	^print[$tree]
</div>


@print[tree;parent_id][locals]
$parent_id(^parent_id.int(0))
$children[$tree.$parent_id]
$result[]

^if($children){
	$result[^children.menu{
		<div>
			<strong>$children.page_id</strong>
			^print[$tree]($children.page_id)
		</div>
	}]
}

Можно обойтись и без корневого элемента page_id = 0 и сэкономить одну связь на каждый элемент, но тогда нужно делать LEFT JOIN, IFNULL(ancestor_id, 0) AS ancestor_id и сложнее доставать всё дерево целиком.

---