Jump to content

advanced: tree copy issue


simpli
 Share

Recommended Posts

Hi,

I have quite an advance issue in my database and I need your input to find out how I should tackle.

I have a table (table A, auto-increment primary key) that represents tree items.

Scenario_id node_id
1 28
1 29

nodes 28 and 29 are children of the same parent.

there is an accompanying table (table B) that tracks the position of the items in the tree. It's called a closure table in the literature.

Scenario_id 	ancestor_id 	descendant_id
1 	28 	28
1 	29 	29
1 	29 	37

(this above means that node 28 is is own ancestor, node 29 also and 37 is a descendant of 29)

 

I am trying to develop a functionality where I can make a copy a whole parent item. Copying the data from table A poses no problem. It would basically copy the items I had earlier. Because of the auto-increment feature however we would have 1 30 and 1 31. At that point however, the copy is not complete. We still need to reproduce the path that the copied parent had. We cannot however copy the data that is in the closure table as it is refering to the original data. My question is therefore what's the easiest way to recreate the path?

 

I think copying each table A line and the corresponding table B lines would be possible but I am not sure this is the most efficient way of dealing with this. Anyone can chip in and maybe point me to ways of handling this i havent tought of?

Thanks in advance.

Link to comment
Share on other sites

yes it does in a sense as it shows one way of organizing the hierarchical tree data. But that's done for me. And it works. Now I'm trying to COPY a node and all its children and it's that operation I'm having trouble conceptualizing.

JR

Link to comment
Share on other sites

yes it does in a sense as it shows one way of organizing the hierarchical tree data. But that's done for me. And it works. Now I'm trying to COPY a node and all its children and it's that operation I'm having trouble conceptualizing.

JR

I think You are using bad tree logic. It is better to realize tree in one table with fields:

 

node_id (PK)

parent_id (ID of parent node)

order (makes order between siblings from same tree level)

 

Top nodes doesn't have parent_id

Then make a recursive function which inputs top node of tree part which you want to copy.

Function should collect all nodes which have provided parent_id, loop through nodes and call itself again for any node until node has descendants.

 

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.