orientdb traverse until condition on node

Question

I'm trying to traverse some nodes and I want to stop the traversal when the furthest reached node matches a certain condition.

My data is some sequentially connected nodes - there is a sample data creation script here (note this is much simplified from my real data, just to illustrate the problem):

create database plocal:people
create class Person extends V
create property Person.name string
create property Person.age float
create property Person.ident integer


insert into Person(name,age,ident) VALUES ("Bob", 30.5, 1)
insert into Person(name,age,ident) VALUES ("Bob", 30.5, 2)
insert into Person(name,age,ident) VALUES   ("Carol", 20.3, 3)
insert into Person(name,age,ident) VALUES   ("Carol", 19, 4)
insert into Person(name,age,ident) VALUES   ("Laura", 75, 5)
insert into Person(name,age,ident) VALUES   ("Laura", 60.5, 6)
insert into Person(name,age,ident) VALUES   ("Laura", 46, 7)
insert into Person(name,age,ident) VALUES   ("Mike", 16.3, 8)
insert into Person(name,age,ident) VALUES   ("David", 86, 9)
insert into Person(name,age,ident) VALUES   ("Alice", 5, 10)
insert into Person(name,age,ident) VALUES   ("Nigel", 69, 11)
insert into Person(name,age,ident) VALUES   ("Carol", 60, 12)
insert into Person(name,age,ident) VALUES   ("Mike", 16.3, 13)
insert into Person(name,age,ident) VALUES   ("Alice", 5, 14)
insert into Person(name,age,ident) VALUES   ("Mike", 16.3, 15)

create class NEXT extends E

create edge NEXT from (select from Person where ident = 1) to (select from Person where ident = 3)
create edge NEXT from (select from Person where ident = 2) to (select from Person where ident = 4)
create edge NEXT from (select from Person where ident = 8) to (select from Person where ident = 12)
create edge NEXT from (select from Person where ident = 5) to (select from Person where ident = 15)
create edge NEXT from (select from Person where ident = 15) to (select from Person where ident = 14)
create edge NEXT from (select from Person where ident = 7) to (select from Person where ident = 13)
create edge NEXT from (select from Person where ident = 13) to (select from Person where ident = 10)

Let's define a sequence as a traversal of nodes from a node with no incoming links (a start node). I am trying to write a query that will return me all sequences of nodes up until the first occurrence of a specific name encountered in the traversal. Suppose the specific name is 'Mike'. So for this data I would want the following sequences to be found:

("Laura", 75, 5) -> ("Mike", 16.3, 15),
("Laura", 46, 7) -> ("Mike", 16.3, 13),
("Mike", 16.3, 8)

I can get the sequence from a specific node to the node just before a 'Mike'. The following query returns me record ("Laura", 75, 5), as the record after that one has name 'Mike'

traverse out('NEXT') from (select from Person where ident = 5) while name <> 'Mike'

The following query returns me two rows, one with record ("Laura", 75, 5) and one with record ("Mike", 16.3, 15), as the record after Mike has name 'Alice'.

traverse out('NEXT') from (select from Person where ident = 5) while name <> 'Alice'

I have two problems with this - firstly I'd like to include the node which matches the condition in the sequence (i.e. when checking for a Person called 'Mike', I'd like 'Mike' to be the final node in the sequence returned)

For that I assume I need to store the traversal in an object, and request one more out-Next for that object before returning. I've tried various approaches to storing the traversal in an object in the middle of a query, but I'm just not getting it. Here is an example (which errors):

select from (
  select $seq  from  
    (select from Person where ident = 5)
    let $seq = traverse out('NEXT') from $current while name <> 'Alice'        
    <... here append the next node ...> 
  )

Secondly, this query just starts from one node - I'd like to start at all starting nodes and return a sequence ending in 'Mike' wherever there is one. I'm hoping that once I can store the traversal in an object, it should be relatively straight-forward to just run from multiple starting points rather than just one.

(Of course, another option for this specific query is to find all the nodes that match the specific condition (e.g. name= 'Mike') and work backwards from those, but I'd really like to see it work in the way I described initially as I'll need that general approach for more things later.)

I suspect quite a lot of my issue is that I'm really struggling to work out how to use the let statement in OrientDB - I'm really not understanding how the scope works, which objects exist at what stages of the query. If anyone knows any good documentation out there other than the official docs that would be really useful as I've read those and I'm still not getting it.

So any helpful hints on how to answer this question, or where to find more information on how to write this type of query would be really useful.

Answer 1

I hope it can help you

select expand($c) let $b=(select expand(out("NEXT")[name="Alice"]) from (select expand(last($a)) from (select from Person where ident = 5)
let $a = (traverse out('NEXT') from $current while name <> 'Alice')) limit 1), $c=unionAll($a,$b)

Answer 2

select name, list from (select name,$c.name as list from Person 
let $b=( select expand(out("NEXT")[name="Alice"]) from (select expand(last($a)) from $parent.$current 
let $a = (traverse out('NEXT') from $current while name <> 'Alice')) limit 1), 
$c=unionAll($a,$b) where in("NEXT").size()=0) 
where list contains "Alice"