1

I am putting together a parts database using the method below for directed acyclic graphs. http://www.codeproject.com/Articles/22824/A-Model-to-Represent-Directed-Acyclic-Graphs-DAG-o

I am able to build my data set using the SQL queries from that page which I have converted to MySQL.

Previously I have used the nested sets model although we found that deletions became a problem. I am unable to find any information on how to traverse the tree using this model. I simply need to be able to create a html tree to show the descendants from a selected parent node and identify leaf nodes (will be using jstree).

I can post the code from the nested sets model if that helps. I don't need any help with the html it is the SQL I am stuck with.

Does anyone have any idea where I can find information on the query I need.

EDIT: Following on from the commments I'd like to adapt to something more closely linked to Bill Karwins closure model. http://www.slideshare.net/billkarwin/models-for-hierarchical-data I notice however that on slides 49-50 which is where I want to select the descendants of a node that the output doesn't seem to provide enough to draw a simple tree. Previously with the nested sets model I was able to get a similar output that would traverse left to right, top to bottom. I'll try to explain.

Item | Depth

1    |   0

2    |   0

3    |   1

6    |   2

7    |   0

9    |   1

This allowed me to draw a tree as the SQL listed the order of descendants in a more manipulatable way. I believe it created "depth" by using a COUNT of subtrees and I will dig out the query if it would be useful here.

Thanks again for all your help.

James Pitt
  • 451
  • 2
  • 8
  • 19
  • Did you have a chance to read through [@BillKarwin's presentation](http://www.slideshare.net/billkarwin/models-for-hierarchical-data), as recommended in [my comment to your previous question](http://stackoverflow.com/questions/12174331/part-database-network-model-hierachal-model-nested-sets-acyclic-graphs#comment16297285_12174331)? It's well worth flicking through: he covers closure tables in the second half with MySQL examples of most typical operations. – eggyal Sep 03 '12 at 15:50
  • I did have a good look through that. I'm confused perhaps by closure tables. It didn't seem to be structured the same as the tables I have created from http://www.codeproject.com/Articles/22824/A-Model-to-Represent-Directed-Acyclic-Graphs-DAG-o I'll take another look... – James Pitt Sep 03 '12 at 16:10
  • Is it essential to include the self links with hop = 0 ? Perhaps that is where I am going wrong. – James Pitt Sep 03 '12 at 16:11
  • Yes, in @BillKarwin's model all reachable nodes (including self) should be present. I haven't looked too closely at the codeproject model, so if it's a little different then I'd take this response with a pinch of salt. – eggyal Sep 03 '12 at 16:17
  • Is it group_concat I'm looking for here? – James Pitt Sep 04 '12 at 10:40
  • If you add a path length, as shown on slide 67, can you not order by that? – eggyal Sep 04 '12 at 10:51
  • I'm not sure I am understanding the difference between DAGs, Edge Tables and Closure Tables. To clarify your previous comment I have used the Closure Table method with path length (hops) and explained why this isn't working for me. http://stackoverflow.com/questions/12265979/closure-tables-is-this-enough-data-to-display-a-tree-view. – James Pitt Sep 04 '12 at 15:49

0 Answers0