Calculating distance between two nodes in SurrealDB

Current version of SurrealDB doesn't have yet graph::shortest_path(node1, node2) function. It sure will be added later to a stable release. However if the task is simplified a little bit we can calculate the distance with the available functions already.

Assume we have a graph of users that resembles a social network where users can connect (make friends). And we want to label the connection distance between two users similar to LinkedIn:

direct connections are labeled as 1st
connections with the shortest path of 2 are labeled as 2nd
connections with the shortest path of 3 are labeled as 3rd
connections with the shortest path of 4 and more are labeled as nth

The data model can be defined as follows:

There are many users
Any user can send a connection request to another user
The other user can accept the connection request

Here is a sample users graph:

graph TD;
  User1-->User4;
  User4-->User5;
  User4-->User2;
  User2-->User6;
  User6-->User3;

Let's create this users graph in SurrealDB:

CREATE user:u1 SET name='User1';
CREATE user:u2 SET name='User2';
CREATE user:u3 SET name='User3';
CREATE user:u4 SET name='User4';
CREATE user:u5 SET name='User5';
CREATE user:u6 SET name='User6';
-- In this example we make all connection requests to be accepted
RELATE user:u1->connect->user:u4 SET accepted=true;
RELATE user:u4->connect->user:u5 SET accepted=true;
RELATE user:u4->connect->user:u2 SET accepted=true;
RELATE user:u2->connect->user:u6 SET accepted=true;
RELATE user:u6->connect->user:u3 SET accepted=true;

Then the query that labels the distance between two users will be as follows:

LET $me = user:u1;
-- Change users to see results for them
LET $user = user:u3;
SELECT * FROM
-- There exist any direct path between $me and $user
-- SELECT VALUE and [0] are used to convert the result to array
IF array::any(
  (SELECT VALUE <->(connect WHERE accepted=true)<->
  (user WHERE id=$user) FROM $me)[0]
) THEN "1st"
-- There exist any 2 levels deep path between $me and $user
ELSE IF array::any(
  (SELECT VALUE <->(connect WHERE accepted=true)<->user<->
  (connect WHERE accepted=true)<->
  (user WHERE id=$user) FROM $me)[0]
) THEN "2nd"
-- There exist any 3 levels deep path between $me and $user
ELSE IF array::any(
  (SELECT VALUE <->(connect WHERE accepted=true)<->user<->
  (connect WHERE accepted=true)<->user<->
  (connect WHERE accepted=true)<->
  (user WHERE id=$user) FROM $me)[0]
) THEN "3rd"
-- Anything else beyod 3rd level marked as "nth"
ELSE "nth"
END;

oskar-gmerek/node_distance.md

Calculating distance between two nodes in SurrealDB