Как правильно сформировать такой запрос?

Question

[serhiops]

Имеется следующая таблица:

Мне нужно получить список всех комментариев к посту, чтобы вывести их подобным образом:

Причем комментарии должны выводитсся в порядке от старых до новых. Пробывал сделать так:

$comments = $post->comments()->whereNull('parent_id')->with(['user', 'childrens'])->get()->toArray();
// код функции comments 
public function comments() {
    return $this->hasMany(\App\Models\Comment::class, 'post_id', 'id');
}

Получаю все корневые комментарии(те, на которые никто не отвечал) + ответы на них, но комментарии, которые являются ответами на некорневые обьекты не берутся с бд.
Как можно сформировать такой запрос не прибегая к большому количеству отдельных запросов? Видел closure-table-comments, но не хочется подключать только для одной страницы. Еще думал создать отдельню колонку root_id, которая будет храить идендификатор корневого обьекта и выбирать по нему.

Comments

[Akina]

Указывайте точно СУБД, включая точную её версию.

Как можно сформировать такой запрос не прибегая к большому количеству отдельных запросов?

Recursive CTE.

PS. Если среднее количество деток у рутов будет невелико (в пределах десятка) - рекомендую перейти от parent-child на materialized path.

Answer 1

[ThunderCat]

Проще всего рут комментарию давать парент_ид равный своему ид, тогда все становится элементарным - ордер по парент_ид, креатед_эт. При выводе просто проверять если парент = ид. А все коментарии которые ответы на ответы все равно писать как ответ на рут коментарий, так как они будут тупо по дате строиться, и разницы в принципе нет. Разве что вы хотите чтобы ветвление было бесконечным, тогда проще ввести рут_ид.