Selecting specific comment subcomments from mysql

Question

PHP

There is a table. comments ( id, date_create, topic_id, user_id, text, user_polls, parent_comment_id)

The comments to the topic (topic) are stored here. Then come the main comments. they have parent_comment_id == 0 .

And subcomments for main comments are stored there, for which parent_comment_id == comments.id .

Question: I know how to select all comments. But I don’t know how to select the last three subcomments for the main comment. Don't make the same request for every main comment. Example: wall in VK. Subcomments are single-level.

Answer 1

SELECT * FROM comments GROUP BY parent_comment_id ORDER BY id DESC LIMIT 3;

Answer 2

Option 1
Add an mpath field and store in it the full path to from the first parent to the current element, then:
SELECT * FROM comments where mpath like 'id_first parent,%' ORDER BY id DESC LIMIT 3;
Option 2
In php level parse parent_id and set first parent for all records

Answer 3

Try Nested Set instead of your approach. From personal experience I will say that it will be much easier for you to work with him.

Answer 4

In this case, I would add a level field to the table to store the nesting level.
And if you also need to build a hierarchy, then Nested Sets, but if you are afraid of loads, then you can additionally store the id of the root comment for each comment - this will be a faster similarity to Nested Sets, but with certain disadvantages.

Answer 5

select everything via select * from `comments` order by parent_comment_id
and then draw recursively on this array and cache.

Answer 6

There is another option for organizing trees like in Bitrix, which is not the simplest solution in technical terms, but significantly saves resources when working with the database www.miraweb-studio.ru/blog/2013/03/bitrix_tree.html