On Wed, 06 Jun 2007 20:59:03 +0200, Steve <St***********@gmail.comwrote:
On 6 Jun, 19:16, gosha bine <stereof...@gmail.comwrote:
>Doesn't this article also explain why you should NOT use adjacency
lists? With recursive queries you're doing N selects to fetch N
elements, that makes using database essentially pointless. From my
experience, ALs are only suitable for very small sets, which can be read
once completely and then processed in memory.
Well, updating serveral hundreds of thousands of left & right values also
puts some strain on the database. I would not like to use a Nested Set for
a regularly changing gigantic tree.
Hi there. Yes it does - and recommends the "Modified Preorder Tree
Traversal" method.
I couldn't get this to work when the tree has several starting nodes
as mine does.
What problem are you facing then? Several startnodes are absolutely no
problem at all....
Would welcome any hints about finding a solution to this and producing
a HTML structured list from the results.
It might look something like this, not thoroughly tested though...
function treelist_nested_set($id = 0,$limit = -1){
$strLimit = ($limit -1) ? " HAVING depth <= ".intval($limit): '';
if($id==0){
$qry = "SELECT node.id,node.name, (COUNT(parent.name) - 1) AS depth
FROM `table` AS node,
`table` AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.id
{$strLimit}
ORDER BY node.lft";
} else {
$qry = "SELECT node.id,node.name, (COUNT(parent.name) -
(sub_tree.depth + 1)) AS depth
FROM `table` AS node,
`table` AS parent,
`table` AS sub_parent,
(
SELECT node.id,node.name, (COUNT(parent.name) - 1) AS depth
FROM `table` AS node,
`table` AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.id = {$id}
GROUP BY node.id
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.id = sub_tree.id
GROUP BY node.id
{$strLimit}
ORDER BY node.lft";
}
$res = mysql_query($qry);
if(!mysql_num_rows($res))
trigger_error('Could not build tree');
return '';
}
$depth = -1;
$return = '';
while($row = mysql_fetch_assoc($res)){
if($row['depth'] < $depth) $return .=
str_repeat("\n</ol>\n</li>",$depth - $row['depth']);
if($row['depth'] $depth) $return .=
str_repeat("\n<ol>\n",$row['depth'] - $depth);
if($row['depth'] == $depth) $return .= '</li>';
$depth = $row['depth'];
$return .= "\n<li>".$row['name'];
}
$return .= str_repeat("\n</ol>\n</li>",$depth)."\n</ol>";
return $return;
}
--
Rik Wasmus
