Geek Answers Handbook

Commentary on the topic with factoring

I hit my head about something and I was wondering if anyone could be more skilled so that I could help me.

My goal is to create a comment stream that affects the comment rating system.

First I will explain where I am now.

Let's say we have a comment on an article that looks like an example below. Parentase number is the identifier of this comment. The identifier is automatically assigned by the database and increases in chronological order with each comment added. The number of dashes in front of the comment text reflects the depth of the comment.

(01)"This is a top level comment." (02)-"This is a second level comment. A reply to the top level comment above." (06)-"This is also a second level comment / another reply to comment 01." (07)--"This is a reply to comment 06." (03)"This is a different top level comment." (05)-"This is a reply to the comment above." (08)--"This is a reply to that comment in turn." (10)---"This is a deeper comment still." (04)"This is one more top level comment." (09)-"This is one more reply."

My first problem was storing this data so that it could be returned in the correct order. If you just store the depth field and arrange by depth, it first returns all top-level comments, and then second-level comments, etc. This is wrong, we must return comments with a complete lack of full lawsuit content.

One way to achieve this is to keep the full content for each comment.

 Comment ID | Parentage 01 | (Comment 01 has no parent because it is top level) 02 | 01- (Comment 02 was a reply to comment 01) 03 | 04 | 05 | 03- 06 | 01- 07 | 01-06- (Comment 07 has two ancestors 01 and then 06) 08 | 03-05- 09 | 04- 10 | 03-05-08-

Adding another comment entry is as simple as grabbing the parent from the comment you are responding to and adding its identifier to generate new source code. For example, if I were to reply to comment 10, I would take it as a parent (03-05-08-) and add its ID (10-). The database will automatically recognize it as the 11th comment, and we will get:

 Comment ID | Parentage 01 | 02 | 01- 03 | 04 | 05 | 03- 06 | 01- 07 | 01-06- 08 | 03-05- 09 | 04- 10 | 03-05-08- 11 | 03-05-08-10-

Now, when we order comments for display, we order on the concatenation of Parentage and the comment ID, which gives us:

 Order by CONCAT(Parentage, ID) Comment ID | Parentage | CONCAT(Parentage, ID) 01 | | 01- 02 | 01- | 01-02- 06 | 01- | 01-06- 07 | 01-06- | 01-06-07- 03 | | 03- 05 | 03- | 03-05- 08 | 03-05- | 03-05-08- 10 | 03-05-08- | 03-05-08-10- 11 | 03-05-08-10- | 03-05-08-10-11- 04 | | 04- 09 | 04- | 04-09-

Which gives the same list as the first. In comment 11, which we later added, inserted in the right place:

 (01)"This is a top level comment." (02)-"This is a reply to the top level comment." (06)-"This is another reply that was posted later than the first." (07)--"This is a reply to the second level comment directly above." (03)"This is a different top level comment." (05)-"This is a reply to the comment above." (08)--"This is a reply to the comment above." (10)---"This is a deeper comment still." (11)----"THIS COMMENT WAS ADDED IN THE EARLIER EXAMPLE." (04)"This is one more top level comment." (09)-"This is one more reply."

Indentation can be done by checking the length of the CONCAT string and multiplying len (CONCAT (Parentage, ID)) by a certain number of pixels. This is great, we have a system for storing comments in a way that recognizes their origin.

Now the problem is:

Not all comments are equal. To evaluate good comments, you need a comment rating system. Say each comment has an up button. While we want to preserve the origin, if one comment has two direct answers at the same level, then we want to show the one who has the highest priorities. I will add a few votes to the [square brackets] below.

 (01)"This is a top level comment." [6 votes] (02)-"This is a reply to the top level comment." [2 votes] (06)-"This is another reply that was posted later than the first." [30 votes] (07)--"This is a reply to the second level comment directly above." [5 votes] (03)"This is a different top level comment." [50 votes] (05)-"This is a reply to the comment above." [4 votes] (08)--"This is a reply to the comment above." [0 votes] (10)---"This is a deeper comment still." [0 votes] (11)----"THIS COMMENT WAS ADDED IN THE EARLIER EXAMPLE." [0 votes] (04)"This is one more top level comment." [2 votes] (09)-"This is one more reply." [0 votes]

In this example, comments (01) and (03) are upper, but (03) has [50 votes], and (01) has only [6 votes]. (01) appears above only due to the fact that it was published earlier and therefore it was assigned a lower identifier. Like (02) and (06), both are answers to (01), but must be reordered to allow one with the majority of votes (06) to rise to the top.

I am completely and completely stuck in trying to achieve this.

I assume that any sorting / reordering and indexing would be better done by voting with comments, rather than loading the page, so the page loading time can be as fast as possible, but apart from that I absolutely do not know!

Any ideas or light that you could shed on possible paths would really take the load off! Thanks for your help, as always.

----------------------------------------------- --- ------------------------------

Edit: In response to @Paddy's decision,

When I run the expression suggested by @Paddy below on the mock data, the first error I get:

 "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

This can be fixed by adding SELECT 'top 100 percent' to the definition of the recursive member. As soon as this is done, I get an error message:

 'CommentTree' has more columns than were specified in the column list.

This can be resolved by adding a Level column to the CommentTree specification. Then it prints the data, but first returns all the top-level comments, and then something similar (but not actually appropriate) the correct sort order after.

Data is returned as such:

 ParentId | CommentId | Comment | Vote | Level NULL | 1 | Text here | 6 | 0 NULL | 3 | Text here | 50 | 0 NULL | 4 | Text here | 2 | 0 4 | 9 | Text here | 0 | 1 3 | 5 | Text here | 4 | 1 5 | 8 | Text here | 0 | 2 8 | 10 | Text here | 0 | 3 10 | 11 | Text here | 0 | 4 1 | 2 | Text here | 2 | 1 1 | 6 | Text here | 30 | 1 6 | 7 | Text here | 5 | 2

Am I doing something wrong or is @Paddy missing something? My apologies, recursive functions are very new to me.

+8
sorting sql mysql hierarchical-data
source share
3 answers

The code below looks good for your task. It is a bit complicated, but for me it was a difficult task to do it in one SELECT . You can split it into multiple SELECT with prefetching into temporary tables (to improve performance) or save them together.

Thanks for the question, it was interesting!

Note that the ParentID for root nodes must be 0 , not NULL .

 DECLARE @a TABLE ( CommentID INT, ParentID INT, Comment VARCHAR(100), Vote INT ) INSERT @a VALUES (1, 0, '', 6), (3, 0, '', 50), (4, 0, '', 2), (9, 4, '', 0), (5, 3, '', 4), (8, 5, '', 0), (10, 8, '', 0), (11, 10, '', 0), (2, 1, '', 2), (6, 1, '', 30), (7, 6, '', 5) ;WITH CTE_1 (ParentId, CommentId, Comment, Vote, Level, LevelPriority, Path) -- prepare base info AS ( SELECT c.ParentId, c.CommentId, c.Comment, c.Vote, 0 AS Level, ROW_NUMBER() OVER(ORDER BY c.Vote DESC), CAST('/' + CAST(c.CommentId AS VARCHAR(32)) AS VARCHAR(MAX)) + '/' FROM @a AS c WHERE ParentId = 0 UNION ALL SELECT c.ParentId, c.CommentId, c.Comment, c.Vote, Level + 1 AS Level, ROW_NUMBER() OVER(ORDER BY c.Vote DESC), d.Path + CAST(c.CommentId AS VARCHAR(32)) + '/' FROM @a AS c INNER JOIN CTE_1 AS d ON c.ParentID = d.CommentID ), CTE_2 (ParentId, CommentId, Comment, Vote, Level, LevelPriority, ChildCount) -- count number of children AS ( SELECT p.ParentId, p.CommentId, p.Comment, p.Vote, p.Level, p.LevelPriority, COUNT(*) FROM CTE_1 AS p INNER JOIN CTE_1 AS c ON c.Path LIKE p.Path + '%' GROUP BY p.ParentId, p.CommentId, p.Comment, p.Vote, p.Level, p.LevelPriority ), CTE_3 (ParentId, CommentId, Comment, Vote, Level, LevelPriority, OverAllPriority, ChildCount) -- calculate overall priorities AS ( SELECT c.ParentId, c.CommentId, c.Comment, c.Vote, c.Level, c.LevelPriority, 1 AS OverAllPriority, ChildCount FROM CTE_2 AS c WHERE Level = 0 AND LevelPriority = 1 UNION ALL SELECT c.ParentId, c.CommentId, c.Comment, c.Vote, c.Level, c.LevelPriority, CASE WHEN c.ParentID = d.CommentID THEN d.OverAllPriority + 1 ELSE d.OverAllPriority + d.ChildCount END, c.ChildCount FROM CTE_2 AS c INNER JOIN CTE_3 AS d ON (c.ParentID = d.CommentID AND c.LevelPriority = 1) OR (c.ParentID = d.ParentID AND d.LevelPriority + 1 = c.LevelPriority) ) SELECT ParentId, CommentId, Comment, Vote FROM CTE_3 ORDER BY OverAllPriority

In this question, I do the following:

  • In CTE_1, I compute the ordering positions within the same parent comment (based on votes) and create a tree path to collect information about all nodes in the hierarchy.
  • In CTE_2, I calculate the number of children belonging to each node +1. The tree path allows you to count the descendants of the entire level by one SELECT .
  • In CTE_3, I calculate the general ordering positions based on three simple rules:
    • The topmost line has position = 1
    • The top child node has position = parent_position + 1
    • The next brother should go after all descendatns of the previous one and has position = prev_sibling_position + prev_sibling_number_of_descendants

EDIT The same solution, but without CTE.

 DECLARE @a TABLE ( CommentID INT, ParentID INT, Comment VARCHAR(100), Vote INT ) INSERT @a VALUES (1, 0, '', 6), (3, 0, '', 50), (4, 0, '', 2), (9, 4, '', 0), (5, 3, '', 4), (8, 5, '', 0), (10, 8, '', 0), (11, 10, '', 0), (2, 1, '', 2), (6, 1, '', 30), (7, 6, '', 5) DECLARE @rows INT DECLARE @temp_table TABLE ( CommentID INT, ParentID INT, Comment VARCHAR(100), Vote INT, LevelPriority INT, Path VARCHAR(MAX), ChildCount INT NULL, OverAllPriority INT NULL ) INSERT @temp_table (CommentID, ParentID, Comment, Vote, LevelPriority, Path) SELECT CommentID, ParentID, Comment, Vote, ROW_NUMBER() OVER(ORDER BY Vote DESC), '/' + CAST(CommentId AS VARCHAR(32)) + '/' FROM @a WHERE ParentID = 0 SELECT @rows = @@ROWCOUNT WHILE @rows > 0 BEGIN INSERT @temp_table (CommentID, ParentID, Comment, Vote, LevelPriority, Path) SELECT a.CommentID, a.ParentID, a.Comment, a.Vote, ROW_NUMBER() OVER(PARTITION BY a.ParentID ORDER BY a.Vote DESC), c.Path + CAST(a.CommentId AS VARCHAR(32)) + '/' FROM @a AS a INNER JOIN @temp_table AS c ON a.ParentID = c.CommentID WHERE NOT a.CommentID IN (SELECT CommentID FROM @temp_table) SELECT @rows = @@ROWCOUNT END UPDATE c SET ChildCount = a.cnt FROM ( SELECT p.CommentID, COUNT(*) AS cnt FROM @temp_table AS p INNER JOIN @temp_table AS c ON c.Path LIKE p.Path + '%' GROUP BY p.CommentID ) AS a INNER JOIN @temp_table AS c ON a.CommentID = c.CommentID UPDATE @temp_table SET OverAllPriority = 1 WHERE ParentID = 0 AND LevelPriority = 1 SELECT @rows = @@ROWCOUNT WHILE @rows > 0 BEGIN UPDATE c SET OverAllPriority = CASE WHEN c.ParentID = p.CommentID THEN p.OverAllPriority + 1 ELSE p.OverAllPriority + p.ChildCount END FROM @temp_table AS p INNER JOIN @temp_table AS c ON (c.ParentID = p.CommentID AND c.LevelPriority = 1) OR (p.ParentID = c.ParentID AND p.LevelPriority + 1 = c.LevelPriority) WHERE c.OverAllPriority IS NULL AND p.OverAllPriority IS NOT NULL SELECT @rows = @@ROWCOUNT END SELECT * FROM @temp_table ORDER BY OverAllPriority
+4
source share

Although I was not directly related to your question, my advice would be to switch to a nested dialing model . I know that there are many alterations, but sooner or later you will realize that this is the best choice :)

+3
source share

Using a table definition something like this (self referencing key):

 Comment ID | Parent ID | Comment | Vote

Then you can use a recursive generic table expression (this is in MS SQL) to get your results:

 WITH CommentTree (ParentId, CommentId, Comment, Vote) AS ( -- Anchor member definition SELECT c.ParentId, c.CommentId, c.Comment, c.Vote, 0 AS Level FROM dbo.Comments AS c WHERE ParentId IS NULL UNION ALL -- Recursive member definition SELECT c.ParentId, c.CommentId, c.Comment, c.Vote, Level + 1 AS Level FROM dbo.Comments AS c INNER JOIN CommentTree AS d ON c.ParentID = d.CommentID Order by C.Vote ) SELECT ParentId, CommentId, Comment, Vote FROM CommentTree

CTE Link:

http://msdn.microsoft.com/en-us/library/ms186243.aspx

0
source share

More articles:


All Articles