Geek asks and answers

What is the best way to store a list / tree of threads in SQL?

I am looking for the best way to save a set of "posts" as well as comments on these posts in SQL. Imagine a design similar to the “Wall” on Facebook, where users can post on their wall and other users can post comments. I need to be able to display all posts on the wall, as well as comments.

When I just started, I came up with a table, for example:

CREATE Table wallposts
(
 id uuid NOT NULL,
 posted timestamp NOT NULL,
 userid uuid NOT NULL,
 posterid uuid NOT NULL,
 parentid uuid NOT NULL,
 comment text NOT NULL
)

id is unique, parentid will be empty in the original messages and will point to id if the line is a comment on an existing post. Easily and very quickly insert new data. However, having made a choice that will bring me back:

POST 1
COMMENT 1
COMMENT 2
POST 2
COMMENT 1
COMMENT 2

, . , , , - 1 , 2 . LEFT JOIN, , , , null.

:

CREATE TABLE wallposts
(
 id uuid NOT NULL,
 threadposted timestamp,
 posted timestamp,
 ...
 comment text
)

, threadposted . , , "" , . :

select * from wallposts order by threadposted, posted;

, . , , . "" , 1/1000 . threadposted , , , . , , , , . .

. node v-left v-right. "", , . , - , . , . , , . .

, , , . , , , , , . , .

, . , . "" , node . , node , , node . , O (n). , , HTML. , ; , , , . , , , .

, SQL, , , JOINS UNIONS -, .

+1
4

, "ParentID" , . , datetimes , , , . , :

Post
----
ID (PK)
Timestamp
UserID (FK)
Text 

Comment
-------
ID (PK)
Timestamp
PostID (FK)
ParentCommentID (FK nullable) -- allows for nested comments
Text
+1

" ". .

SQL Server 2008, hierarchyID".

, ), .

EDIT: , , . ( ).

0

, , .. ?

, , , , SQL (, )

SELECT posts.id,
       posts.posted AS posted_at,
       posts.userid AS posted_by,
       posts.posterid,
       posts.comment AS post_text,
       comments.posted AS commented_at,
       comments.userid AS commented_by,
       comments.comment AS comment_text
FROM   wallposts AS posts
LEFT OUTER JOIN wallposts AS comments ON comments.parent_id = posts.id
ORDER BY posts.posted, comments.posted

, , , , .

0

If we stick to your table design ... I think you'll need a special value in the parentid column to separate the original posts from the comments (maybe just NULL if you change the definition of this column to nullable). Then self-connection will work. Something like that:

SELECT  posts.comment as [Original Post],
comments.comment as Comment 
FROM   wallposts AS posts
LEFT OUTER JOIN wallposts AS comments 
ON posts.id=comments.parentID
WHERE posts.parentID IS NULL
ORDER BY posts.posted, comments.posted

The result set shows the original message before each comment and has the correct order.

(This was done using SQL Server, so I'm not sure if it works in your environment.)

0
source

More articles:


All Articles