The objective is to implement a recursive queries capability in Mathematica similar with ORACLE connectBy operator.It allows to navigate through hierarchical data structures like organizational data, employee hierarchy or more general tree like structures. You could find more details on hierarchical queries at : https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm#i2069380The new function syntax :connectBySQL [Input table , {initial node condition} , {connect statement between current node and previous node } , {association rules containing calculations }, {optional additional conditions} ]
Load SQL package
Load SQL package
Load the last version of SQLOperators package containing SQL function developed so far.
See my previous post : SQL operators as Wolfram Language functions (https://community.wolfram.com/groups/-/m/t/2195893)
The full package could be find @
https://www.wolframcloud.com/env/damcalrom/Published/SQLOperators.wl
See my previous post : SQL operators as Wolfram Language functions (https://community.wolfram.com/groups/-/m/t/2195893)
The full package could be find @
https://www.wolframcloud.com/env/damcalrom/Published/SQLOperators.wl
In[]:=
CloudImport["https://www.wolframcloud.com/env/damcalrom/Published/SQLOperators.wl"];
Sample data
Sample data
tbtree=tableSQL[{{Missing[],1,"Node1"},{1,2,"Node2"},{1,3,"Node3"},{1,4,"Node4"},{2,5,"Node5"},{3,6,"Node6"}},{"parent","id","name"}];tbtree//showTableSQL
Total Rows:6 Elapsed:1.×
-7
10
Out[]//TableForm=
parent | id | name |
Missing[] | 1 | Node1 |
1 | 2 | Node2 |
1 | 3 | Node3 |
1 | 4 | Node4 |
2 | 5 | Node5 |
3 | 6 | Node6 |
Graphical representation
Graphical representation
tbtree//TreeGraph[#// whereSQL[#,!MissingQ[parent]]&// selectSQL[#,{parent,id}]&// pullSQL, VertexLabelsrowTransformSQL[#,<|"name"(idname)|>]// pullSQL[#,{name}]&]&
Out[]=
ConnectBySQL
ConnectBySQL
connectBySQL[tbtree, MissingQ[parent],(*initialnodescondition*) parentid,(*connectbyconditionforeachiterationonchildnodes*) <|"parent"parent,(*columnspaircontainingnodesrelationshipmustbyincluded*) "id"id, "name"name, "level"connectByRecurse[level+1,0],(*connectByRecursedefinesrecursivecalculationand initialvalue*) "path"connectByRecurse[path<>"-"<>name,name] |>(*calculatedcolumns*) ]//showTableSQL
Total Rows:6 Elapsed:0.004343
Out[]//TableForm=
parent | id | name | level | path |
Missing[] | 1 | Node1 | 0 | Node1 |
1 | 2 | Node2 | 1 | Node1-Node2 |
1 | 3 | Node3 | 1 | Node1-Node3 |
1 | 4 | Node4 | 1 | Node1-Node4 |
2 | 5 | Node5 | 2 | Node1-Node2-Node5 |
3 | 6 | Node6 | 2 | Node1-Node3-Node6 |
Recurse through all nodes and identify subtrees
Recurse through all nodes and identify subtrees
tballsubtress=connectBySQL[tbtree, True,(*takeallnodesasroot*) parentid,(*connectbyconditionforeachiterationonchildnodes*) <|"parent"parent,(*columnspaircontainingnodesrelationshipmustbyincluded*) "id"id, "level"connectByRecurse[level+1,0],(*connectByRecursedefinesrecursivecalculationand initialvalue*) "path"connectByRecurse[path<>"-"<>name,name], "subtree"connectByRecurse[subtree,id](*identifyallthesubtrees*) |>(*calculatedcolumns*) ];tballsubtress//showTableSQL[#,UpTo[100]]&tballsubtress//whereSQL[#,subtree3]&//showTableSQL
Total Rows:13 Elapsed:1.×
-7
10
Out[]//TableForm=
parent | id | level | path | subtree |
Missing[] | 1 | 0 | Node1 | 1 |
1 | 2 | 0 | Node2 | 2 |
1 | 3 | 0 | Node3 | 3 |
1 | 4 | 0 | Node4 | 4 |
2 | 5 | 0 | Node5 | 5 |
3 | 6 | 0 | Node6 | 6 |
1 | 2 | 1 | Node1-Node2 | 1 |
1 | 3 | 1 | Node1-Node3 | 1 |
1 | 4 | 1 | Node1-Node4 | 1 |
2 | 5 | 1 | Node2-Node5 | 2 |
3 | 6 | 1 | Node3-Node6 | 3 |
2 | 5 | 2 | Node1-Node2-Node5 | 1 |
3 | 6 | 2 | Node1-Node3-Node6 | 1 |
Total Rows:2 Elapsed:0.0002018
Out[]//TableForm=
parent | id | level | path | subtree |
1 | 3 | 0 | Node3 | 3 |
3 | 6 | 1 | Node3-Node6 | 3 |
Starting from bottom up (from leafs up to root )
Starting from bottom up (from leafs up to root )
With[(*getsallavailableparents*) parents=tbtree//projectSQL[#,{parent}]&//whereSQL[#,!MissingQ[parent]]&//pullSQL[#,{parent}]&, tbtree// addColumnsSQL[#,<|"isleaf"!ContainsAny[parents,{id}]|>]&//(*calculateleafnodesasnodesthatarenotparents*) connectBySQL[#, isleaf,(*startfromleafs*) idparent,(*connectbyconditionforeachiterationonparentnodes*) <|"parent"parent,(*columnspaircontainingnodesrelationshipmustbyincluded*) "id"id, "isleaf"isleaf, "name"name<>" test", "level"connectByRecurse[level+1,0],(*connectByRecursedefinesrecursivecalculationand initialvalue*) "path"connectByRecurse[path<>"-"<>name,name], "subtree"connectByRecurse[subtree,id], "sum"connectByRecurse[id+sum,id](*cumulativesumofchildnodes*) |>(*calculatedcolumns*) ]&// showTableSQL[#,UpTo[100]]&]
Handling hierarchies with cycles
Handling hierarchies with cycles
Sample data containing cycles.
Node1 is ancestor and also child of Node6.
Node1 is ancestor and also child of Node6.
We get Infinite loop as we didn’t define criteria for eliminating cycles
Adding criteria for eliminating cycles as last function argument
Donations
Donations