0

I am planning on making a comment-reply system inside of a site. (A simple one)

I searched through youtube related to this, and I found a good source. The problem is, he has a 2 separate queries, one for parent comment and two for reply to parent comment and what's more is that the second query is inside a foreach loop (bad practice). So, I need to revised of how he query the comments and the tables.

This is the table I came up with:

comment_table
id | post_id | comment_id | date | message | reply_id

//In the source, he has 2 tables, parent and child comment table.

I want to create a single query where parent comments and child comments are get back at the same time, but I am not sure if it will work.

Let's say I have a table like this.

id | post_id | comment_id |     date | message | reply_id
 1 |       2 |          3 | jan 2016 |   hello |
 2 |       2 |          4 | feb 2016 |  hello2 |
 3 |       2 |          5 | mar 2016 |  hello3 |        3

Note: reply_id is where the comment_id replied to.

I need to output this results like this:

Comments

hello2 //comment
hello //comment
   hello3 //reply

This is what I need to do:

  • Get all comments in DESC order by date while checking if reply_id is not empty, and if not empty, it will check it based on reply_id in DESC order by date.

My problem is, the I only thing that I know how to query is the first part and that is to Get all comments in DESC order by date, the rest is I do not know.

My question is, is this possible to query? If so, what syntax do I need here? If you have a better solution than mine, please tell me.

0

The name reply_id is a bit confusing. Consider in_reply_to or parent_id.
I assume that post_id is the id for the entire thread? Maybe it should be called thread_id? ("Post", to me, is somewhat synonymous with "post".)

I don't think you need two tables, at least not for comments. However, if you do have information about the thread as a whole, I recommend a separate table for that; it would have PRIMARY KEY(thread_id).

What's the problem with this?

SELECT ...
    FROM ...
    WHERE thread_id = 2
    ORDER BY id DESC;

Can have multiple replies to a single comment? If so, you should change the question to make that clear. It adds a level of complexity.

You seem to have a "hierarchical" tree. But, since you have post_id, the above query should get all of the messages, approximately in chronological order.

Why have comment_id if you already have id?

Or, start over...

  • Information about the entire Thread is in a separate table, and identified by thread_id.
  • The Comments are in a second table, with PRIMARY KEY(thread_id, comment_id). comment_id can simply be an AUTO_INCREMENT.
  • Each comment in a "thread" has the same thread_id.
  • Each row has a unique comment_id.
  • "Replies" need to identify their parent_id.
1
  • Yes, post_id is like thread_id. comment_id is the parent comment, while reply_id is the child comment or simply a reply to the (parent) comment. In your 4th list, comment_id might not be necessarily unique because, a user can reply in the same thread infinite times. Jan 18 '17 at 2:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.