Graph database Comparison relations some advantages

　　　　The previous article briefly introduced some basic contents of the graph database (first, SQL Server2017 graph Database (a)), this article through the comparison of some syntax to reflect the graph database schema some advantages, such as convenient query, easy to understand the statement.

Advantages of building queries on the graph database model:

T-SQL brings some new syntax to the chart query. In the SELECT statement we have some special statements to relate the points and edges. Let's walk through some, build query statements to retrieve posts and replies, as follows:

1. We retrieve two parts of each record, post and reply, so we need to refer to the two forumposts ' table in the FROM clause, this place can take some meaningful aliases:

　　　　

FROM dbo. Forumposts replypost, dbo. Forumposts Repliedpost

2. Although we can choose any alias, it is best to choose a meaningful name when working with the graph object.
3. We need the relationship between "posts" and this relationship is table reply_to '. The syntax is as follows:

　　　　

FROM dbo. Forumposts replypost, dbo. Reply_to, dbo. Forumposts Repliedpost

4. In the WHERE clause, we need to correlate all the tables, using the match statement below to implement the association:

From dbo. Forumposts replypost, dbo. Reply_to, dbo. Forumposts repliedpostwhere MATCH (replypost-(reply_to)->repliedpost)

5. This syntax is interesting: the "-" dash represents the $From of the Edge _id field represents the relationship, and then the "-and" dash and the greater-than-sign $To _id field of the edge represents the relationship.
6. Knowing that alias has reply, that alias has replied post, we can build a list of query fields:

From dbo. Forumposts replypost, dbo. Reply_to, dbo. Forumposts repliedpostwhere MATCH (replypost-(reply_to)->repliedpost)

7. Queries for the same functionality in the relational model are as follows:

     Select Repliedpost.postid,repliedpost.posttitle,     as  as Replytitle    from forum.forumposts replypost, forum.forumposts repliedpost   where Replypost.postid=repliedpost.replyto

8. These queries are similar and of course the match syntax is easier to understand.
9. After executing the above statement, the query results are as follows:

10. We add the name of the person who wrote the reply. You need to add the 'forummembers ' node and the ' written_by ' side in the FROM clause. The statements are as follows:

FROM dbo. Forumposts replypost, dbo. Reply_to, dbo. Forumposts repliedpost, dbo. Forummembers Repliedmember, Written_by repliedwritten_by

11. Also add the internal relationship to the match statement:

     WHERE MATCH (replypost-(reply_to)->repliedpost-(repliedwritten_by)->repliedmember)

12. This allows you to add the name of the reply person to the select list, and the final query is as follows:

     -- Posts, members and replies   SELECT Repliedpost.postid,repliedpost.posttitle,repliedmember.membername,     as as replytitle from      dbo. Forumposts replypost, dbo. Reply_to, dbo. Forumposts repliedpost,        dbo. Forummembers Repliedmember, written_by repliedwritten_by   WHERE MATCH (replypost-(reply_to), repliedpost-(repliedwritten_by)->repliedmember)

13. The corresponding query in the relational model is as follows:

SELECT Repliedpost.postid,repliedpost.posttitle,     as  as Replytitle,   Repliedmember.membername from  forum.forumposts replypost, forum.forumposts repliedpost,   Forum.ForumMembers Repliedmember  WHERE Replypost.postid=repliedpost.replyto and        repliedpost.ownerid= Repliedmember.memberid

14. The results are as follows:

15. The name of the reply object is also missing. Add ' forummembers ' and ' written_by ' in the FROM clause as above:

FROM dbo. Forumposts replypost, dbo. Reply_to, dbo. Forumposts repliedpost,dbo. Forummembers Repliedmember, written_by repliedwritten_by,dbo. Forummembers Replymember, Written_by replywritten_by

16. Next, modify the match clause,' replymember ' needs to be associated with ' Replypost ', but how do you deal with this relationship without affecting other relationships? It needs to be implemented in a different way:

WHERE MATCH (replymember<-(replywritten_by)-replypost-(reply_to)->repliedpost-(repliedwritten_by), Repliedmember)

17. Note that the symbol "<-" is in the opposite direction as before, but the meaning is the same: one in the side table's $to _id The relationship to the node table.
18. Finally, you need to add the name of the member who wrote the reply, with the following code:

--Posts and members and their replies   and members SELECT Repliedpost.postid, Repliedpost.posttitle, Repliedmember.membername,   asas  replytitle,   replymember.membername [ Replymembername] from      dbo. Forumposts replypost, dbo. Reply_to, dbo. Forumposts repliedpost,        dbo. Forummembers Repliedmember, written_by repliedwritten_by,        dbo. Forummembers Replymember, written_by replywritten_by      WHERE MATCH (replymember<-(replywritten_by)- replypost-(reply_to)->repliedpost-(repliedwritten_by)->repliedmember)

19. The result set is as follows:

20. The corresponding statement in the relational query:

SELECT Repliedpost.postid,repliedpost.posttitle,           as Replyid,        as Replytitle, Replymember.membername  From Forum.forumposts Replypost, forum.forumposts repliedpost,       forum.forummembers Repliedmember, Forum.forummembers replymember  WHERE replypost.postid=Repliedpost.replyto        and Repliedpost.ownerid=Repliedmember.memberid and        replypost.ownerid=replymember.memberid

21. At this point, it may be more difficult to read in a relational pattern as the relationship grows, whereas the match clause is much easier in the graph data schema. Let's take a look at some interesting and useful places in the graph data pattern.

Count the number of replies per post

  SELECT distinct repliedpost.postid,repliedpost.posttitle,          repliedpost.postbody,          count ( Replypost.postid) over (partition by Repliedpost.postid)              as  totalreplies from   dbo. Forumposts replypost, reply_to, dbo. Forumposts repliedpost   WHERE MATCH (replypost-(reply_to)->repliedpost)

In this statement we count the number of each reply, but only in one level, not in the tree structure of the entire reply.

List of root stickers (main stickers)

We get all the root stickers by using the following statements that do not use match:

SELECT Post1.postid,post1.posttitle  FROM dbo. Forumposts Post1  in (select from dbo. Reply_to

The match syntax simply allows us to correlate three or more entities (such as two nodes and a relationship). When we want to associate only two of them, we only need a regular connection or subquery. As in the above statement.

Add the ' Level ' field to the results

Add a ' Level ' field to display the tree structure. In T-SQL there is a simple syntax called a CTE to implement recursion. However, there is a problem, you cannot use the match syntax on a derived table, you can use the CTE at this time. If necessary, match can be used in the CTE, but the reverse is not possible, and there is a limitation. Here's a look at using a regular relationship to iterate using only a CTE, with the following code:

With Root as  ( Select$node _id asNode_id,rootposts.postid, Rootposts.posttitle,1  asLevel,0  asReplyTo fromdbo. Forumposts rootpostswhere$node _id notinch(Select$from _id fromdbo.reply_to) Union AllSelect$node _id,replypost.postid, Replypost.posttitle, level+1  as[level], root. PostID asReplyTo fromdbo. Forumposts Replypost, reply_to, RootwhereReplypost. $node _id=reply_to. $from _id and root.node_id=reply_to. $to _id)SelectPostid,posttitle, level, ReplyTo fromRoot

Retrieve all replies from a post

Using the CTE recursive syntax, we can retrieve all replies for a post with a tree structure. If you use regular syntax, you cannot retrieve post 1 by retrieving the 3, because 3 is the reply to 2, and 2 is the reply to 1. Use a CTE. You can retrieve post 3 when you are querying all replies to posts 1. The code is as follows:

With Root as  ( Select$node _id asNode_id,rootposts.postid,rootposts.posttitle,1  asLevel,0  asReplyTo fromdbo. Forumposts rootpostswherePostid=1Union AllSelect$node _id,replypost.postid, Replypost.posttitle, level+1  as[Level],root. PostID asReplyTo fromdbo. Forumposts Replypost, reply_to, RootwhereReplypost. $node _id=reply_to. $from _id and root.node_id=reply_to. $to _id)SelectPostid,posttitle, level, ReplyTo fromRoot

We can also do this in reverse, retrieving all the parent stickers sequentially in the tree structure. Since the CTE does not support outer JOIN, it is added externally with the following code:

With Root as  ( SelectLeafpost. $node _id asNode_id,leafpost.postid, Leafpost.posttitle fromdbo. Forumposts LeafpostwhereLeafpost.postid=3--Single post Union allSelectRepliedpost. $node _id asNode_id,repliedpost.postid, Repliedpost.posttitle fromdbo. Forumposts Repliedpost, reply_to, RootwhereRoot.node_id=reply_to. $from _id and reply_to. $to _id=repliedpost. $node _id)SelectRoot. Postid,root. Posttitle, Repliedpost.postid Parentpostid fromRoot left Join reply_to on root.node_id=reply_to. $from _id LEFT JOIN dbo. Forumposts repliedpost on reply_to. $to _id=repliedpost. $node _id

Retrieve all posts for a user

Query one with all the information, unlike the post, this does not need a tree, to be simple and many:

--Peter Reply to all posts SELECT distinct repliedpost.postid,repliedpost.posttitle, repliedpost.postbody from DBO.F Orumposts replypost, reply_to, dbo. Forumposts repliedpost, dbo. Forummembers members,written_by WHERE MATCH ( members<-(written_by)-replypost-(reply_to),repliedpost) and Members.membername='Peter'--all posts from Peter SELECT Replypost.postid,replypost.posttitle,replypost.postbody, Repliedpost.postid ReplyTo F ROM dbo. Forumposts replypost, reply_to, dbo. Forumposts repliedpost, dbo. Forummembers members,written_by WHERE MATCH ( members<-(written_by)-replypost-(reply_to),repliedpost) and Members.membername='Peter'

Perhaps you notice that the difference between the two queries above is whether distinct is used on the display field. This goes to the weight because Peter can reply to the same post more than once.

Retrieving likes in a model (likes)

This query is interesting: the ' likes ' side is the relationship between the member and the posting table. Each relationship is unique and unaffected by other relationships. The code is as follows:

--like a post or a post that someone likes.  SELECT Post.postid,post.posttitle,member.membername  From   dbo. Forumposts Post, likes,         dbo. Forummembers Member  WHERE MATCH (Member-(likes)-Post)  - likes the person or the person likes the  SELECT Member.memberid,member.membername Likemember,         likedmember.membername likedmembername from  dbo. Forummembers Member, likes, dbo. Forummembers likedmember  WHERE MATCH (Member-(likes)->likedmember)

It's also easy to aggregate information to get the total likes of each post or each member.

--The total likes per postSelectPost.postid,post.posttitle, COUNT (*) Totallikes fromdbo. Forumposts post,likes, dbo. Forummembers MemberswhereMatch (members-(likes),Post) GROUP by Postid,posttitle--total number of likes per memberSelectLikedmembers.memberid,likedmembers.membername, COUNT (*) Totallikes fromdbo. Forummembers members,likes, dbo. Forummembers likedmemberswhereMatch (members-(likes),likedmembers) Group by Likedmembers.memberid, Likedmembers.membername

User likes and replies to posts

We can also create some more interesting queries, for example, looking for people who like and reply to these points, as follows:

  SELECT Member.membername,member.memberid,         likedpost.postid,likedpost.posttitle,         Replypost.posttitle replytitle from  dbo. Forumposts likedpost, reply_to, dbo. Forumposts replypost,       likes, dbo. Forummembers Member, written_by  WHERE MATCH (Member-(likes)->likedpost<-(reply_to)-replypost-( written_by)->member)

Note that the ' Member ' node is used two times in the same match expression. This forms a filter: Members who are like and have a reply, need to have records in both ' likedpost ' and ' Replypost ' .

Then the code in the relational mode is as follows:

Select likes.memberid,members.membername    from Forum.likes likes, forum.forumposts Posts,       Forum.forummembers  memberswhere likes.memberid=posts.ownerid and   posts.replyto= Likes.postid and   members.memberid=likes.memberid

It seems that this writing is more difficult to understand and read.

Replies to members of multiple posts

SELECT Members.memberid, Members.membername,           as Total  From   dbo. Forumposts replypost, reply_to, dbo. Forumposts repliedpost,         written_by,dbo. Forummembers members  WHERE  MATCH (members <-(written_by)-replypost-(reply_to),repliedpost )  GROUP by MemberID, Members.membername  >1

Replies to a post more than one member:

SELECT Members.memberid, Members.membername,         Repliedpost.postid Repliedid,count ( as totalreplies from   dbo. Forumposts replypost, reply_to, dbo. Forumposts repliedpost,       written_by,dbo. Forummembers members  WHERE MATCH (members <-(written_by)-replypost-(reply_to),repliedpost)  GROUP by Memberid,membername,repliedpost.postid  Having count (*) >1

The only difference between the two statements is the aggregation of the display results.

Summary

　　By comparing the queries and associations in the graph data model, the common statements and the same queries in the relational schema are compared, it is easy to find that both in legibility, logical understanding and performance are greatly improved. Of course, this is only the first version, so there are a lot of problems, the next I would describe this version of the issue of a part.

Graph database Comparison relations some advantages