CODEWITHSUNDEEP
orientdb
lee
lee

Reputation: 246

OrientDB query for hierarchical data

OrientDB Server v2.0.10 , I am trying to come up with a query for the following scenario.

I have 2 hierarchies: A->B->C and D->E->F The number of nodes in the hierarchy can change. The node in 1st hierarchy can be connected to the other hierarchy using some relation say 'Assigned'. What I want is the parent node of the 2nd hierarchy if there is any incoming edge to any of the node in that 2nd hierarchy from the 1st.

For example, say we have Car-Child->Engine-Child->Piston and Country-Child->State-Child->City And a relationship Made_In which relates Car or Engine or Piston to either Country or State or City So if there is a relation with either of Country or State or City, the Country should be returned. Example, Engine1-Made_In->Berlin, this would return Germany. Sorry for such a toyish example. I hope it is clear.

Thanks.

Upvotes: 2

Views: 1622

Answers (1)

aw31n
aw31n

Reputation: 161

You should consider reading the chapter about "traversing" - that should be the missing link to answer your question. You can find it here: http://orientdb.com/docs/last/SQL-Traverse.html

Basically, if you think of your graph as a family tree, you want to achieve 3 things:

  • Find all children, grand-children, grand-grand-children (and so on) from tree 1 for a given family member (=Hierarchy1)

  • Find those who have relations to members of another family tree (=ASSIGNED)

  • Show me who's on top of this tree (=Hierarchy2)

One of the possible solutions should look a little something like this:

Since you want to end up on top of hierarchy2, you have to start on the other side, i.e. hierarchy1.

  1. Get hierarchy1 (top-to-bottom)

     TRAVERSE out("CHILD") FROM Car

  2. Choose all relations 

    SELECT out("MADE_IN) FROM ([1])

  3. and from those, go bottom-to-top

    TRAVERSE in("CHILD") FROM ([2])

  4. Who's on top?

    SELECT FROM ([3]) WHERE @class="Country"

Combined into one sql, it looks as ugly as this:

    SELECT FROM (
       TRAVERSE in("CHILD") FROM (
          SELECT out("MADE_IN") FROM (
             TRAVERSE out("CHILD") FROM Car
          )
       )
    ) WHERE @class="Country"

You could replace Car with any @rid in hierarchy1 to get a list of countries it or any part of it was made in.

There might be better solutions for sure. But at least this one should work, so I hope it will help.

Upvotes: 3

Related Questions