CODEWITHSUNDEEP
mysql
Appl3s
Appl3s

Reputation: 177

Single query for nested comments

In an effort to create an efficient method for displaying nested comments I am hoping that it's possible to do with one query.

Simplified comment table

CREATE TABLE comment (
comment_id int unsigned primary key auto_increment,
date int unsigned,
parent_id int unsigned,
comment text);

To clarify, comments are to be ordered by date ASC. If a comment has a parent_id which is not null, that means it is a nested comment. This parent_id refers to said comments' parent comment_id.

This latter part is where I am having difficulty. Can anyone help me come up with a single mysql query to do this please?

Thank you

Upvotes: 2

Views: 992

Answers (1)

YashTD
YashTD

Reputation: 438

The following code should cater to your second need in an efficient way:

CREATE TABLE comment(
comment_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
date INT UNSIGNED,
parent_id INT UNSIGNED DEFAULT NULL,
commend TEXT,
FOREIGN KEY fk_parent(parent_id)
REFERENCES comment(comment_id)
ON UPDATE CASCADE
ON DELETE RESTRICT)

You just want to ensure that a child comment isn't inserted before a parent comment because of the foreign key reference.

However, when it comes to ordering your table by date, the MySQL CREATE TABLE syntax includes no such functionality and I think an effective implementation would be to simply ORDER BY while querying. Bear in mind, however, if you do choose to alter the table at a later stage and order it by the date, that any operations post this alteration will not adhere to this order. Specifically:

ALTER TABLE comment ORDER BY date ASC

will only order the present data in your table and not any subsequent data entering it.

Also, consider using a DATE data type instead of int, depending on your requirement.

Upvotes: 1

Related Questions