nested data, copying and moving tree branches

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pht
Forum Newbie
Posts: 7
Joined: Sat May 22, 2004 6:47 am

nested data, copying and moving tree branches

Post by pht »

Hi

I've been messing with this problem some days. I have tried MPTT method to collect and organize data in hieracrhical structure, but still donno, is it the best method or should i continue with next theme:

DB:nodes
node_id
node_ppath
node_depth

DB:datas
data_id
data_node_id
data_type
data_title
data_content
data_order
data_created
data_modified
data_uid
data_gid
data_chmod

Now i can make selects, inserts, updates and deletes quite simple, but i haven't get solution, how to copy or move branches of tree to another location. Copying node or first childs of node goes well, but how would you copy (moving bases propably to the same method) all the children of some node?

Let me give you example structure for nodes (ppath is parent path presentation with dot separated list):

Code: Select all

id    ppath     depth
1     0          0
2     1          1
3     1          1
4     1.2        2
5     1.2.4      3
6     1.2.4.5    3
7     1.2.4      2
8     1          1
9     1.8        2
10    1.8.9      3
11    1.8.9      3
12    1.3.7      3
Now i would like to copy branch starting from id 4 to id 11. That means all items between 4-7 will be copied and final table looks like this:

Code: Select all

13   1.8.9.11             4
14   1.8.9.11.13          5
15   1.8.9.11.13.14       6
16   1.8.9.11.13          5
I suppose, these is no straight method with sql to do this, but i should make some php functions to deal with case. Any function suggestions?

This could be also on php code forum, but didin't know which one is better in this case. Administrators could move this post if required.

Thanks,
Marko
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

While waiting for another answer....

You may find http://www.php.net/explode useful to break down the ppath. I would convert the path information into arrays and modify them with an offset. Then you could use http://www.php.net/implode to rebuild them.

A link page detailing trees can be found here http://troels.arvin.dk/db/rdbms/links/. For Postgres http://www.grzm.com/fornow/archives/200 ... ierarchies.

I generally use the adjacency list model detailed here http://www.sitepoint.com/print/1105
pht
Forum Newbie
Posts: 7
Joined: Sat May 22, 2004 6:47 am

Post by pht »

!!!Materialized Path!!! That maybe the keyword. Yeah, thanks for reference. I didn't found that document by myself when "googling", only repeating same documents from sitepoint. Going forward...
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

there is a pear package for nested sets (a different implementation of tree structures in sql)
pht
Forum Newbie
Posts: 7
Joined: Sat May 22, 2004 6:47 am

Post by pht »

If you mean DB_NestedSet, i've tried that too...
Post Reply