sqlorientdbfoaf

OrientDB SQL recursivness


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'))

Solution

  • 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'