Request to fetch comments

Question

MySQL

I am writing a small website. I want to attach comments to articles on the site. I want to make them multi-level as in Habré.
The minimum table structure is as follows:
- comment_id - comment id
- parent_comment - parent comment
- comment_text - comment itself

how to make a query that will select a comment by id and all its child comments?

Answer 1

It has already been called nested sets twice - but it has a large overhead for changing data. Although, 10k comments are a rarity, so you have to move very quickly.
habrahabr.ru/post/46659/
For comments, even for mysql (postres is much more fun with its recursive queries) - better, IMHO, exactly the Adjacency List will do (that is, exactly the structure that you figured out). But you will have to build a tree already in the application logic - select the entire table and build an array[ parent_id ][] = node. When outputting, go into recursion.

Answer 2

Add another topic_id and make a selection based on it.

Answer 3

Search for "Nested Sets". On Habré, including quite a lot of articles.
goo.gl/UnA1h

Answer 4

read about nested sets - this is a technique that allows you to store tree structures in the database so that most of the selections are made in one SQL query. en.wikipedia.org/wiki/Nested_set_model
In short, two more fields are added to each record, which are then used for selections. The selection is simplified but the insertion becomes more complicated.
If the site is small and you don’t want to deal with nested sets (although it’s very useful, and there are plenty of ready-made implementations), then you can do anything - from recursive selection with several SQL queries to a crutch with the addition of another field of the string type, in which to write “ path" to this element in the tree (for example, node IDs separated by the symbol "0>12>45>") and then select this field using LIKE

Answer 5

Don't listen to these talkers. For comments, the ideal option is Materialized Path (dear colleagues who offer Nested Sets, have you tried to figure out how many updates you have to do when inserting a comment before 100 existing ones? and before 200?). The problem of expensive insertion is partially circumvented by switching to fractional left and right keys, but this is difficult.
Disadvantages of Materialized Path: limited depth of comments (but you can set, for example, 16 or 32 - this is almost always enough), limit on the number of child comments (again, put a number like 65536 and don’t worry - it’s unlikely that your site will have so many answers).
Changing the parent of a comment is an expensive and complicated operation, but I don't know where it is required.
Pros: cheap insert (1 normal INSERT), cheap fetch (1 SELECT with index).
Conclusion: better listen to my smart advice and not these talkers.

Answer 6

The question has already been answered, but I want to add that you can not implement anything.
You can take ready-made and use, for example: disqus.com/ Usage
example:
slyweb.ru/jquerydoc/position.php

Answer 7

bonart.org.ua/node/52