Re: nested set tree model help
| From: | Tomas V.V.Cox | Date: | Thu, 21 Mar 2002 05:02:02 +0000 |
| Subject: | Re: nested set tree model help | ||
| References: | 1 | Groups: | php.pear.dev |
| Request: | Send a blank email to pear-dev+get-5066@lists.php.net to get a copy of this message | ||
Wolfram Kriesing wrote:
>
> can someone help?
>
> may some people have noticed that i commited some methods which work
> on the nested set tree thingy, in the Tree-classes
> now i was wondering if someone could help me on some issues
> (Tomas???) the problems i have are actually just forming some
> queries for - getChildren, to get all direct children of a node
> - getParent, getting the parent of a node
> i didnt find anything on the web :-(
Easy, introducing a "parent_id" column :-) It really makes things more
easy.
At least for the direct parent without a parent_id column do something
like:
select count(*) as level from table t1, table t2 where t1.left between
t2.left and t2.right and t1.id=25;
or
$sql = "select * from table where left < $node_left and right >
$node_right".
"order by right";
$res = $db->limitQuery($sql, 0, 1);
$parent = $res->fetchRow();
(something like get only the first node from a ordered getPath())
Direct childrens without a parent_id is more difficult (at least I can't
think in a "one query" way for doing that). Perhaps with some mix of
UNIONs and LIMITs or SUBSELECTs could work but those statements aren't
common to all the backends.
A useful trick is the one to get the deep of a node. To do it just do a
php count() over the data array returned of your getPath() or doing the
following query:
"select count(t1.name) as level from table t1, table t2
where t1.left between t2.left and t2.right and t1.id=<NODE ID>";
With the following you could list the levels of all the nodes:
"select count(*) as level, t1.* from table t1, table t2 where t1.left
between t2.left and t2.right group by t1.name"
There are other tips at:
http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci537290,00.html
> as you might have seen, some methods are already implemented
> examples for those that work exist in the directory examples
> i would really appreciate some help
> just the queries would be enough
getNext() and getPrevious() depends directly on getChildren().
For introducing transactions in remove/add I would recommend something
like so:
$db->autocommit(false); // Check is transactions are supported
// by the backend here
do {
$error = $db->query("INSERT ...");
if (DB::isError($error)) {
break;
}
// more queries here
} while (false);
if (DB::isError($error)) {
$db->rollback();
$db->autocommit(true);
return raiseError($error);
}
$db->commit();
$db->autocommit(true);
Hope that helps,
Tomas V.V.Cox