CODEWITHSUNDEEP
sql-servergraph-databasessocial-networkingsql-server-graph
Beingnin
Beingnin

Reputation: 2422

Get all friends in SQL Server graph database

I'm trying to retrieve all friends of a particular person using SQL Server graph processing

Person table as node

╔════╦═══════╗
║ Id ║ Name  ║
╠════╬═══════╣
║  1 ║ David ║
║  2 ║ Suraj ║
║  3 ║ John  ║
║  4 ║ Ryan  ║
╚════╩═══════╝

likes as an edge//for simplicity I am not using auto-generated ids here

╔════╦═══════╦═══════╦═══════════════════╗
║ Id ║ From  ║  To   ║      Remarks      ║
╠════╬═══════╬═══════╬═══════════════════╣
║  1 ║ David ║ Suraj ║ David likes Suraj ║
║  2 ║ David ║ John  ║ David likes John  ║
║  3 ║ John  ║ Ryan  ║ John likes Ryan   ║
╚════╩═══════╩═══════╩═══════════════════╝

My graph query to find all friends of John would be like this

select p1.name, p2.name [friend]
from person p1, likes l, person p2 
where p1.name = 'John' and match(p1-(l)->p2)

and this will return the below result set

╔══════╦════════╗
║ name ║ friend ║
╠══════╬════════╣
║ John ║ Ryan   ║
╚══════╩════════╝

The problem is we got all the people John likes, which excludes the people who likes John(in this case David). In real world, if a person is a friend of me, I am also a friend of him right?. I know I can use union here to find all people who likes John and add up to the above. But It will make the case worst for scenarios where finding friend of friends. Can we do it more intuitively tweaking with the Match or the arrows

Expected Result

+------+--------+
| Name | Friend |
+------+--------+
| John | Ryan   |
| John | David  |
+------+--------+

update: expected result added

Upvotes: 2

Views: 733

Answers (2)

andowero
andowero

Reputation: 499

SQL Server graph is directional. So you

  • either need to add both edges if you want the friendship to be mutual.
  • or, because MATCH doesn't allow OR, you would have to use UNION of two queries where each uses the directionality differently:
select p1.name, p2.name [friend]
from person p1, likes l, person p2 
where p1.name = 'John' and match(p1-(l)->p2)

UNION

select p2.name, p1.name [friend]
from person p1, likes l, person p2 
where p2.name = 'John' and match(p1-(l)->p2)

Upvotes: 1

bechbd
bechbd

Reputation: 6341

The reason you are getting this answer is due to the directed nature of the edge.

In real world, if a person is a friend of me, I am also a friend of him right?

This depends on the domain. For example, this is true for Facebook or LinkedIn, however it is not true for something like Twitter where just because you follow me does not mean I follow you. This is a design decision you need to understand when developing a graph data model.

I am not familiar with the syntax for SQL Graph but you can likely traverse the edges bidirectionally by removing the > from the MATCH step like this:

select p1.name, p2.name [friend]
from person p1, likes l, person p2 
where p1.name = 'John' and match(p1-(l)-p2)

At least this is how you would do it in another graph pattern matching syntaxes. If this doesn't work then you will likely have to do a UNION.

Upvotes: 2

Related Questions