Mysql – How to properly query this

MySQL

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.

Best Answer

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.
Related Question