How to get the number of replies for all nested comments?

Question

PHP

Hey!
There is a table:
comments:
id
user_id
message
comment_id -- refers to comments
Is it possible to get the following result with an SQL query?

[
                'id'            => 1,
                'comment_id'    => null,
                'replies_count' => 2,
                'nested'        => [
                    'id'            => 2,
                    'comment_id'    => 1,
                    'replies_count' => 1,
                    'nested'        => [
                        'id'            => 3,
                        'comment_id'    => 2,
                        'replies_count' => 0,
                    ],
                ],
            ],

Answer 1

Google "hierarchical query"
like this: https://urvanov.ru/2017/05/28/%D0%B8%D0%B5%D1%80%D...