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?
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