|
|
Posted by Rik on 06/06/07 20:24
On Wed, 06 Jun 2007 20:59:03 +0200, Steve <StevePBurgess@gmail.com> wrot=
e:
> On 6 Jun, 19:16, gosha bine <stereof...@gmail.com> wrote:
>
>> 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 r=
ead
>> once completely and then processed in memory.
Well, updating serveral hundreds of thousands of left & right values als=
o =
puts some strain on the database. I would not like to use a Nested Set f=
or =
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 =3D 0,$limit =3D -1){
$strLimit =3D ($limit > -1) ? " HAVING depth <=3D ".intval($limit): '=
';
if($id=3D=3D0){
$qry =3D "SELECT node.id,node.name, (COUNT(parent.name) - 1) AS dep=
th
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 =3D "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 =3D {$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 =3D sub_tree.id
GROUP BY node.id
{$strLimit}
ORDER BY node.lft";
}
$res =3D mysql_query($qry);
if(!mysql_num_rows($res))
trigger_error('Could not build tree');
return '';
}
$depth =3D -1;
$return =3D '';
while($row =3D mysql_fetch_assoc($res)){
if($row['depth'] < $depth) $return .=3D =
str_repeat("\n</ol>\n</li>",$depth - $row['depth']);
if($row['depth'] > $depth) $return .=3D =
str_repeat("\n<ol>\n",$row['depth'] - $depth);
if($row['depth'] =3D=3D $depth) $return .=3D '</li>';
$depth =3D $row['depth'];
$return .=3D "\n<li>".$row['name'];
}
$return .=3D str_repeat("\n</ol>\n</li>",$depth)."\n</ol>";
return $return;
}
-- =
Rik Wasmus
[Back to original message]
|