[Oracle]Learn hierarchical queries with Harry Potter

Published on 29 octobre 2013

Trees, trees everywhere!

A hierarchy is a structure of items organized by levels, the global structure is often call a tree. It is one of the most common way of organizing objects and it can be a lot of things :

  • ….

Typically an hierarchical query would be:

« Who are the employees that John is in charge of? »

« Which are the species under the Hominidae family? »

« How many folders does my hard drive contain? »

Meet the Weasleys

So the visual representation of a hierarchy/tree is easy, however the data modeling is a bit different. I’ll take a simple case to illustrate this: a family tree. In a family every member is a person with two parents/ancestors (which are persons too): so one table to gather them all!

Here is an small portion of the Weasley family:

weasley-family-tree

 

The tree has 3 levels (3 generations): Arthur and Molly are the roots and their grand children are the leafs. Let’s get these people into a table:

CREATE TABLE weasleys 

(

     child varchar(30),

     ancestor varchar(30)

);

Insert Into weasleys (child,ancestor) values ('Ron','Arthur');

Insert Into weasleys (child,ancestor) values ('Ron','Molly');

Insert Into weasleys (child,ancestor) values ('Ginny','Arthur');

Insert Into weasleys (child,ancestor) values ('Ginny','Molly');

Insert Into weasleys (child,ancestor) values ('Hugo','Ron');

Insert Into weasleys (child,ancestor) values ('Hugo','Hermione');

Insert Into weasleys (child,ancestor) values ('Rose','Ron');

Insert Into weasleys (child,ancestor) values ('Rose','Hermione');

Insert Into weasleys (child,ancestor) values ('James','Ginny');

Insert Into weasleys (child,ancestor) values ('James','Harry');

Insert Into weasleys (child,ancestor) values ('Albus','Ginny');

Insert Into weasleys (child,ancestor) values ('Albus','Harry');

Insert Into weasleys (child,ancestor) values ('Lily','Ginny');

Insert Into weasleys (child,ancestor) values (&#039;Lily&#039;,&#039;Harry&#039;);</code></pre>

Who’s your daddy?

And now Oracle voodoo…nah actually it’s quite simple. By using ‘CONNECT BY PRIOR’ and ‘START WITH’ you can achieve almost everything. The only thing to get is which direction you want to go: to the roots? to the leafs?

To the roots

For example: Name all the ancestors of ‘Albus’. The search will ‘START WITH’ Albus and we want to get Albus ancestor who is also the child of Albus ancestor’s ancestor, which translates in :

connect by prior ancestor = child

start with child = &#039;Albus&#039;;

ANCESTOR

------------------------------

Ginny

Arthur

Molly

Harry

Nice! Let’s add a little something : the pseudo-column ‘LEVEL’, it’s pretty self explainatory (start level is 0) and concatenate all that:

connect by prior ancestor = child

start with child = &#039;Albus&#039;

group by level;

LEVEL NAMES

----- ------------------------------------------------------------

    1 Ginny,Harry

    2 Arthur,Molly

To the leafs

And the other way: Every child and grand child of Molly:

SQL> select child, ancestor, level from weasleys t

  2  connect by prior child = ancestor

  3  start with ancestor = &#039;Molly&#039;

  4  order by level asc,ancestor;

CHILD                          ANCESTOR                            LEVEL

------------------------------ ------------------------------ ----------

Ron                            Molly                                   1

Ginny                          Molly                                   1

James                          Ginny                                   2

Albus                          Ginny                                   2

Lily                           Ginny                                   2

Hugo                           Ron                                     2

Rose                           Ron                                     2
  
comments powered by Disqus