Search code examples
orientdbgraph-databases

OrientDB query to return matched paths satisfying constraints in tabular form

I'm new to OrientDB and am trying to put together a query that finds paths that match a template in a fairly simple structure.

The structure looks like this:

Nodes

Node Classes

Edge Descriptions

Edge Connectivity

  • "A" nodes will only have one "E1" edge connecting it to one "B" node.
  • "C" nodes will always have TWO "E2" edges connecting it to two different "B" nodes.

Sample Data

I've loaded a small dataset which looks like this:

Sample Graph

Query

I'm trying to put together a query to find the paths that include the "C" nodes that exceed a weight threshold, include the two connected "B" nodes and the "A" nodes on the other end have the same date as the "C" node.

So, I'm be looking for the paths that match this pattern:

[A1] -(E1)-> [B1] <-(E2)- [C1] -(E2)-> [B2] <-(E1)- [A2]

Ideally I'd like to get the result into a tabular form with each row capturing each path:

C.Date | C.Weight | B1.ID | B2.ID | A1.Flag | A2.Flag

I've been looking through the documentation on the OrientDB website for MATCH and TRAVERSE but the examples there are mostly "friend of a friend" type searches and I've had a difficult time mapping that to my problem so far.

My initial attempts used nested SELECT and TRAVERSE queries, but I ran into difficulty getting the Date fields in "A" nodes to match what was in the "C" node two hops away...

So I switched to a MATCH query which I think is getting me close. I get a graph that looks correct in the Graph View within the OrientDB browser app using this query:

MATCH {class: C, as: C, where: (Weight>0.8 AND Date.format('yyyy-MM')='2014-09')}.out('E2') 
    {as: B}.in('E1') 
    {as: A, where: ($matched.C.Date = $currentMatch.Date) } 
RETURN $elements

but it doesn't really get me to the tabular result that I'm hunting for. I can get two rows with the C-B1-A1, and C-B2-A2 paths by changing RETURN to this:

RETURN C.Date as Date, C.Weight as Weight, B.ID, A.Flag

which returns:

 Date               | Weight | B_ID | A_Flag
--------------------+--------+------+--------
2014-09-10 00:00:00 | 0.95   | 1    | A 
2014-09-10 00:00:00 | 0.95   | 2    | A

but that's not quite what i'm looking for. I'd like to think that I'm close to my desired result with my current query, but I'm not sure. Does the whole query need to be restructured?

I'm open to making a few schema modifications if necessary, but I'd prefer to avoid putting in fields in the vertices that tell me the same thing as the edges do (i.e., adding a C.B_ID1 and C.B_ID2 field) if possible.

All help is greatly appreciated!

Solution

  • try this

    select Date, Weight, list(B_ID)[0] as B1_ID, list(B_ID)[1] as B2_ID, list(A_flag)[0] as A1_flag, list(A_flag)[1] as A2_flag
from
(
MATCH {class: C, as: C, where: (weight>10 AND date.format('yyyy-MM')='2014-09')}.out('E2')
    {as: B}.in('E1') {as: A, where: ($matched.C.date=$currentMatch.date)}

RETURN C, C.date as Date, C.weight as Weight, B.ID, A.flag
)
group by C

    using your data I'm obtaining this results: screenshot

    Hope this helps! Ivan