SQL Server graph to fetch multiple node types connected to a node

Question

I'm planning to use SQL Server 2019 graph features for one of my project. The data scheme would look something like the picture below.

Given the user (Id: 2356, name: Mark), I would want to retrieve all the Posts and the Tweets done by the user's follower ordered by when it was posted or when it was tweeted together with a limit/pagination on the overall result.

As of now, I don't know of a better way other than doing 2 separate queries & manually handling pagination, which makes it inefficient & also cumbersome if we add another new edge type in future in addition to Posted/Tweeted.

Are there better ways to address such usecases in SQL Server graph ?

SELECT mainUser.*, followingUser.*, followerPost.*
FROM 
     User mainUser, Follows userFollows, User followingUser, Posted followerPosted, Post followerPost 
WHERE 
     MATCH (mainUser-(userFollows)->followingUser-(followerPosted)->followerPost)
AND
     mainUser.id=2356
ORDER BY
     followerPosted.posted_on desc

SELECT mainUser.*, followingUser.*, followerTweet.*
FROM 
     User mainUser, Follows userFollows, User followingUser, Tweeted tweeted, Tweet followerTweet 
WHERE 
     MATCH (mainUser-(userFollows)->followingUser-(tweeted)->followerTweet)
AND
     mainUser.id=2356
ORDER BY
     tweeted.tweeted_on desc

score 0 · Answer 1 · answered Dec 22 '21 at 13:06

Use heterogenous edge or node view. See answer https://stackoverflow.com/a/70055567/3434168.

---- there may be column colisions due to UNION ALL so fix them as you need
---- it doesn't matter which columns you select in your view
---- the MATCH algorithm (probably) uses metadata of the VIEW
CREATE VIEW v_SecondTierEdges AS
  SELECT *, 'Tweeted' AS Type FROM Tweeted
  UNION ALL
  SELECT *, 'Posted' AS Type FROM Posted
GO

CREATE VIEW v_SecondTierNodes AS
  SELECT tweeted_on AS did_that_on, 'Tweet' AS Type FROM Tweet
  UNION ALL
  SELECT posted_on AS did_that_on, 'Post' AS Type FROM Post
GO

SELECT
  mainUser.*, followingUser.*, followerTweet_or_Post.*
FROM 
  User mainUser, Follows userFollows, User followingUser, v_SecondTierEdges tweeted_or_posted, v_SecondTierNodes followerTweet_or_Post 
WHERE 
  MATCH (mainUser-(userFollows)->followingUser-(tweeted_or_posted)->followerTweet_or_Post)
AND
  mainUser.id=2356
ORDER BY
  tweeted_or_posted.did_that_on desc

SQL Server graph to fetch multiple node types connected to a node

1 Answers1