Is there a solution for pagination of tree comments?

Question

PHP

Friends. Tell me, is there any solution for pagination of tree comments?
It is necessary to make a communication system similar to this: shikimori.org (1 parent comment, the rest with nesting of 2 levels) with PAGINATION! Those. there should be a "show more comments" button for both the first level and the second level.
From everything I read on the Internet, I found two solutions:
First option:
www.9lessons.info/2011/05/facebook-wall-script-wit...
Make two tables in the database:
1 table - all first-level comments.
Table 2 - all comments of the second level with the ID key from the first level, by which they are loaded to the comment of the first level.
those.

select комменты первого уровня
while() {
if (есть ответы на коммент) {
select комменты второго уровня where parent = id коммента первого уровня
}
}

But this implementation is very resource-intensive, right? Doing Select in a while loop, as I understand it, is not good?
10 first-level comments per page are planned. If these 10 comments have child comments, then there will be 11 requests.
Pros of this implementation :
In the first query, when SELECT all first-level comments, you can set a clear LIMIT. You can also set a clear LIMIT for child comments. These LIMITs are needed for pagination (the ability to upload more comments, like here: easycaptures.com/fs/uploaded/1045/7634907040.jpg)
Cons: High load on the database?!
------------------------------------------------------
The second option:
One table with all comments.
We make only 1 query to the database - SELECT of all comments to the news, along with their children, sorted by date.
We get an array with all the comments to the news.
We process this array with a recursion function:

function dotree(&$comments, $parentComment = 0, $level = 0, $count = null){    
  if (is_array($comments) && count($comments)){         
    $return = array();
    if (is_null($count)){
      $c = count($comments);
    }else{
      $c = $count;
    }
    for($i=0;$i<$c;$i++){       
      if (!isset($comments[$i])) { continue; }
      $comment = $comments[$i];
      $parentId = $comment['parent'];
      if ($parentId == $parentComment){
          
        $comment['level'] = $level;
        $commentId = $comment['id'];
        $return[] = $comment;
        unset($comments[$i]);
        while ($nextReturn = dotree($comments, $commentId, $level+1, $c)){
          $nextReturn = array_reverse($nextReturn);
          $return = array_merge($return, $nextReturn);
        }
      } 
    }    
    return $return;
  }
  return false;
}

We get an array with all the comments to the news, like:
1
1.1
1.2
2
3
3.1
3.2
3.3
How to display the first 3 parent comments with child subcomments? If we put LIMIT 3 in this single query, then the first three records will be selected, sorted by date. Not the fact that there will be 3 parent.
Since it is reasonable to make the possibility of pagination (navigation) by comments, so that you can load a larger number of comments with additional. requests via json?
I have been looking for a reasonable solution for a couple of days, please someone tell me.

Answer 1

Start by looking at how you can store trees in an RDBMS.
For a limited amount of nesting - the materialized path is great. And it is simply displayed by limit, you can even do without offset if you are puzzled.

Answer 2

Firstly, you need two tables, this is nonsense, all you need is the ParentId field, this is the foreign key field on the Id of the comment above the level.
Id: 1 ParentId: null,
Id:2 ParentId: 1,
Id:3 ParentId: 1,
Id:4 ParentId: null,
Id:5 ParentId: 4
1 That all Level 1 messages have ParentId == null.
2. In each message, you need to enter the CreateDate field, in which the time the message was created will be written (DO NOT CONFUSE WITH EDITING TIME, if possible). This field is useful for you to sort the most recent message (ORDER BY DESC and TOP 3 will help you).
3. When you first load the page, you need a query to the TOP 10 database where ParentId == null, and for each such take the TOP 3 pre-sorted ORDER BY DESC CreateDate.
You need a backend method that will accept ParentId and page number, if you load comments of the first level, then ParentId will be null, if the second level, then you will receive the Id of the base comment. Well, the second point is the page number, there is nothing complicated.
Skip already displayed comments on the first load, if we are talking about comments of the first level (ParentId == null), then you need Skip(10), if the second level, then Skip(3).
Do not forget that comments are added to you all the time, if you took the 3 most recent comments at the time of loading, then you need to take into account that by the time you clicked on the load, there were 10 more new comments, you could already comment on three new ones and then you will probably download the same three messages again at some point. How to solve the problem? It depends on how everything should work for you, you can pass an additional Id of the last message to the method and take TOP (10) where the date is < the comment found by Id. Or if you need to upload new ones, then you need to think something more seriously, either with the button to upload new messages, or check if there are new ones while uploading, etc. ... Your decision is needed here.

Answer 3

I suggest to implement everything on ajax. Stupidly click on the links, pass the parameters, and then, depending on the parameters, display the desired list of comments in the desired div with ID.
Moreover, logically, to display any level of the list, the same function is used both in ajax and in php. Only the passed parameters change (for example, block id).