CODEWITHSUNDEEP
orientdb
Marc Bernier
Marc Bernier

Reputation: 2996

OrientDb Vertices with most Mutual Friends

I'm trying to find the pairs of vertices that have the greatest number of common vertices between them. It is very similar to the 'number of mutual friends' example used in many graph database demos. I can determine the number of mutual vertices between a pair of known vertices using this:

SELECT Expand($query) LET
  $query1 = (SELECT Expand(outE().in) FROM #1:2,
  $query2 = (SELECT Expand(OutE().in) FROM #1:3,
  $query = Intersect($query1,$query2);

The Count() of the above query's result is the number of common vertices.

However, I can't figure out how to aggregate that query across my entire data set. My best solution has been a brute force, where I iterate through each vertex and run the above query against all other vertices (technically, I do all the vertices 'after' that vertex).

My solution is inefficient and had to be coded in C# rather than done entirely in SQL. How can this be done using OrientDb's SQL?

Upvotes: 0

Views: 140

Answers (2)

Marc Bernier
Marc Bernier

Reputation: 2996

Slight modification to @Luigi's answer:

SELECT a, b, Count(friend) AS nFriends FROM (
  MATCH
    {class:Person, as:a} -E- {as:friend} -E- {class:Person, as:b, where:($matched.a != $currentMatch)}
    RETURN a, b, friend
) GROUP BY a, b ORDER BY nFriends DESC

I needed the GROUP BY or I just get one big count.

Upvotes: 1

Luigi Dell'Aquila
Luigi Dell'Aquila

Reputation: 2814

You can use a SELECT with a MATCH:

SELECT FROM (
  SELECT a, b, count(friend) as nFriends from (
    MATCH
      {class:Person, as:a} -FriendOf- {as:friend} -FriendOf-{as:b, where:($matched.a != $currentMatch)}
    RETURN a, b, friend
  )
) ORDER BY nFriends

Upvotes: 1

Related Questions