OrientDB SQL recursivness

Question

I have a working query, that returns all the co actors of Tom Hanks (basically a friend of a friend problem).

  SELECT COUNT(name) as name FROM (
    SELECT expand(out('ACTS_IN').in('ACTS_IN')) FROM Actor WHERE name = 'Tom Hanks'
) WHERE name <> 'Tom Hanks'

Now I'm trying to construct a query that would give me co actors of co actors (friends of friends of friend)

My query, that doesn't finish

SELECT count(DISTINCT(name)) as coactor FROM (
SELECT expand(out('ACTS_IN').in('ACTS_IN').out('ACTS_IN').in('ACTS_IN').out('ACTS_IN').in('ACTS_IN')) FROM Person WHERE name = 'Tom Hanks'
)
WHERE name <> 'Tom Hanks'

The question is, can you stack .in and .out like this:

expand(out('ACTS_IN').in('ACTS_IN').out('ACTS_IN').in('ACTS_IN').out('ACTS_IN').in('ACTS_IN'))

Answer 1

In your case you could use the TRAVERSE() function to retrieve all the records linked by the edge ACTS_IN.

Structure:

create class Person extends V
create class Movie extends V
create class acts_In extends E
create class directed extends E
create class friend extends E
create class rated extends E

create property Person.name String
create property Person.surname String
create property Movie.title String

create vertex Person set name="Tom", surname="Hanks"
create vertex Person set name="Robin", surname="Wright"
create vertex Person set name="Helen", surname="Hunt"
create vertex Person set name="Robert", surname="Zemeckis"
create vertex Person set name="Russell", surname="Crowe"
create vertex Person set name="Ben", surname="Affleck"
create vertex Person set name="Kevin", surname="Macdonald"
create vertex Person set name="John"
create vertex Person set name="Mark"
create vertex Person set name="Paul"
create vertex Person set name="Mel", surname="Gibson"
create vertex Person set name="Nancy", surname="Meyers"
create vertex Movie set title="Forrest Gump"
create vertex Movie set title="Cast Away"
create vertex Movie set title="State of Play"
create vertex Movie set title="What Women Want"

create edge acts_In from (select from Person where name="Tom" and surname="Hanks") to (select from Movie where title="Forrest Gump")
create edge acts_In from (select from Person where name="Tom" and surname="Hanks") to (select from Movie where title="Cast Away")
create edge acts_In from (select from Person where name="Robin" and surname="Wright") to (select from Movie where title="Forrest Gump")
create edge acts_In from (select from Person where name="Robin" and surname="Wright") to (select from Movie where title="State of Play")
create edge acts_In from (select from Person where name="Helen" and surname="Hunt") to (select from Movie where title="Cast Away")
create edge acts_In from (select from Person where name="Helen" and surname="Hunt") to (select from Movie where title="What Women Want")
create edge acts_In from (select from Person where name="Mel" and surname="Gibson") to (select from Movie where title="What Women Want")
create edge acts_In from (select from Person where name="Russell" and surname="Crowe") to (select from Movie where title="State of Play")
create edge acts_In from (select from Person where name="Ben" and surname="Affleck") to (select from Movie where title="State of Play")
create edge friend from (select from Person where name="Mel" and surname="Gibson") to (select from Person where name="Helen" and surname="Hunt")
create edge friend from (select from Person where name="Ben" and surname="Affleck") to (select from Person where name="Russell" and surname="Crowe")
create edge directed from (select from Movie where title="What Women Want") to (select from Person where name="Nancy" and surname="Meyers")
create edge directed from (select from Movie where title="Cast Away") to (select from Person where name="Robert" and surname="Zemeckis")
create edge directed from (select from Movie where title="Forrest Gump") to (select from Person where name="Robert" and surname="Zemeckis")
create edge directed from (select from Movie where title="State of Play") to (select from Person where name="Kevin" and surname="Macdonald")
create edge rated from (select from Movie where title="What Women Want") to (select from Person where name="Paul")
create edge rated from (select from Movie where title="Cast Away") to (select from Person where name="John")
create edge rated from (select from Movie where title="Forrest Gump") to (select from Person where name="Mark")
create edge rated from (select from Movie where title="State of Play") to (select from Person where name="John")

Query 1: Retrieve all of the actors/co-actors/ecc... of 'Tom Hanks' (except himself)

SELECT expand(DISTINCT) FROM (SELECT DISTINCT(@rid) FROM (TRAVERSE BOTH('ACTS_IN') 
FROM (SELECT FROM Person WHERE name = 'Tom' AND surname = 'Hanks') MAXDEPTH 4) 
WHERE name <> 'Tom' and surname <> 'Hanks')

Query 2: Retrieve the count of the actors/co-actors/ecc... of 'Tom Hanks' (except himself)

SELECT COUNT(DISTINCT(@rid)) AS co_actors FROM (TRAVERSE BOTH('ACTS_IN') 
FROM (SELECT FROM Person WHERE name = 'Tom' AND surname = 'Hanks') MAXDEPTH 4) 
WHERE name <> 'Tom' and surname <> 'Hanks'