0

I am looking at created a piece of forum software that will have Semi-Threaded topic structure. I am writing sudo code for how to best handle the fetching of posts in the proposed environment. In a non-threaded environment the posts are selected by post_id as post ID would be auto incremented and thus could be ordered simply to get posts from newest to oldest or vis versa.

 SELECT * from posts_tables
 WHERE topic_id = X
 ORDER by post_id ASC
 LIMIT 20

In a semi threaded environment you would want to also select posts in the same manner to get the latest posts for a thread, but you would also want to select the replies that each of those posts had. Which is simply done with 2 SQL calls, but I was wondering if perhaps a better method exists only using one. With 3 Indexed fields, being post_id, topic_id and replied_to_post_id.

This time you get these results and select again afterwards

 SELECT * FROM posts_tables
 WHERE topic_id = X
 ORDER by post_id ASC
 LIMIT 20

GET RESULTS THEN

SELECT * FROM posts_table
WHERE replied_to_post_id = X,Y,Z

Is there a better manner of doing this?

If you don't understand Semi-Threaded topic structure is exactly, then below link might be of great use to use. http://www.bulletinboards.com/ThreadHelp.cfm

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Case
  • 4,244
  • 5
  • 35
  • 53
  • You may be looking for a recursive query: http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql – Andreas May 23 '14 at 22:09
  • Thank you for you're reply. I appreciate you helping. I was thinking there may be something programatically that I am not aware of in building something in this structure. I am familiar in how to do a recursive query, but I am not sure that would save any processing. – Case May 23 '14 at 22:12
  • With a recursive query you could, given a post id, return that post and all replies, and all replies to those replies, and all replies to those replies, and so on. Is that the goal? – Andreas May 23 '14 at 22:15
  • The structure you are talking is is what reddit is. That is a Threaded Formatted topic system. I am only building a Semi-threaded topic system. – Case May 23 '14 at 22:17
  • I don't grok the semi-threaded nature you are looking for. Never the less I'd be glad to help. Perhaps you could post some example data and the results you'd like. – Andreas May 23 '14 at 22:21
  • If you check out the link in the bottom of the post it shows you what one is. I love the stranger in strange land reference. – Case May 23 '14 at 22:23
  • I'm not in a position to navigate there atm; I'll try to take a look this weekend. Never thirst, friend. – Andreas May 23 '14 at 22:28

0 Answers0